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
Group (but never both) could be null. Additionally, (
Category) should always be unique and (
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.