0

Currently I have a table as follows:

ID    Name    Category    Group
 1     Abc       D1111    55501
 2     Abd       D1234    55502
 3     Abc       D1112    55545
 4     Abc       D1113    55545    // Group can repeat for distinct Name, Category
 5     Cdb       B3345     NULL
 6     Cdb       B3399     NULL
 7     RRR        NULL    12345
 8     RRR        NULL    23456

ID is my primary key. Name should never be null, however either of Category or Group (but never both) could be null. Additionally, (Name, Category) should always be unique and (Name, Group) should be unique where Category is null.

I'm wondering if implementing this with unique indexes is the best way to go or if I should somehow split this into more than one table. If I were to use indexes it might look like this:

CREATE UNIQUE INDEX index1 ON FakeTable (Name, Category) 
WHERE Category IS NOT NULL;

CREATE UNIQUE INDEX index2 ON FakeTable  (Name, Group) 
WHERE (Category IS NULL) AND (Group IS NOT NULL);

CREATE UNIQUE INDEX index3 ON FakeTable  (Name, Category, Group) 
WHERE (Category IS NOT NULL) AND (Group IS NOT NULL);

Mainly this table is to provide a list of category/group combinations for each Name. So in that case I might query SELECT Category, Group FROM FakeTable WHERE Name = 'Abc'.

Also, I have other tables that reference this one via ID (although ID is a surrogate key, so in actuality it is Name/Group that usually serve as the reference fields).

I'm hoping someone can tell me if this is a bad strategy and why.

6
  • 1
    Can you provide an example of the queries you will be doing on this table? For example, are you making a "SELECT ... FROM table WHERE Name='value' and (Category='value' or Group='value')" How do you query it? This has an impact on the indexes you should be creating if you want SQL to use the index you will be creating. Oct 4 '19 at 17:17
  • Sure. Mainly this table is to provide a list of category/group combinations for each Name. So in that case I might query SELECT Category, Group FROM FakeTable WHERE Name = 'Abc'. Oct 4 '19 at 17:24
  • Also, I have other tables that reference this one via ID (although ID is a surrogate key, so in actuality it is Name/Group that usually serve as the reference fields.) Oct 4 '19 at 17:31
  • Oh yeah you're right. I suppose I should implement a check constraint to ensure that one of Category/Group is not null? Oct 4 '19 at 20:34
  • 2
    Do NOT call your variables (table and/or column names) using SQL Keywords. It makes code buggy and difficult to read, is a real pain if you want to port your application and it just makes NO sense whatsoever! Use group_ or groupe (French) or gruppe (German) or your language of choice - in short, anything but group! Look here.
    – Vérace
    Oct 5 '19 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.