two separate tables or two uniquifying foreign keys
Hi we have SQL Server 2008 R2 although we are looking to upgrade in the next year or 2. But my question is... Several tables have a foreign index to a specific ID. Now for that ID, we will actually have two IDs. The reason is we are taking our product and basically splitting it into two, one for the physical part of it and one for the software part. This should have been done this way at the start but we are doing it now. The physical and software portions would both have common fields in several of these tables. So we will either have to have double the tables or mention both foreign keys in each table. Which way should we go? Currently we have something like TableA A_ID - clustered primary key FKID int not null DateField date or datetime2(0) not null and supporting columns with a unique index on FKID, DateField And the options are: Option 1 TableA A_ID - clustered primary key FKID int NULL FKID2 int NULL FKID1And2Combo int NOT NULL - which would be FKID multiplied by 1,000,000 plus FKID2. We could easily make the decision to always have FKID or FKID2 but not both in the same entry. DateField and supporting columns with unique index on FKID1And2Combo , DateField non-unique index on FKID, DateField non-unique index on FKID2, DateField For this option we would basically have to add one column to our existing forms. option 2 TableA A_ID - clustered primary key FKID int not null DateField date or datetime2(0) not null and supporting columns with a unique index on FKID, DateField TableB B_ID - clustered primary key FKID2 int not null DateField date or datetime2(0) not null and supporting columns with a unique index on FKID2, DateField For this option we would everywhere have to reference both tables--have 2 different forms, one for each table.
When you say FKID1and2Combo, do you mean you're thinking about having a calculated column that literally combines the two columns? If so, don't do that. That would require parsing to deal with lots of different queries. If you mean you're going to have a compound primary key consisting of two different columns, that can be a fine approach, but only if both columns are always used. It looks like you said that sometimes it's going to be one and sometimes the other that gets filled in. In that case, the compound approach is going to seriously break down. Let's say you're querying for ID1 and it's the first key in the compound index. It'll work great. But now you query for ID2 and it's the second key. It's not in the histogram of the statistics, so your queries aren't going to be able to use it. If this relationship is nullable in this fashion, it means that you actually have two different relationship sets going on. The better way to deal with this is to have an interim table that identifies the current relationship. You can either do that at the top of the chain and then use it for all the underlying tables, or, you'll need a relationship table for each table, but that design is going to be very difficult to maintain over time. Better the one master table that identifies that this product, in this instance, is either a software or a physical product. Now, logically, that could be a compound key still, but it's up to you how you set that up.