question

EuniceRH avatar image
EuniceRH asked

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.
indexforeign-keyunique
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK I see for Option 1, I could have both nullable foreign keys in one table, then make a unique index on 3 columns: both of them plus the date. I see that can happen. I just wonder if anyone has input as to which way I should go. TIAFYH, Eunice
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hard to say not knowing the queries. Any filtering that needed ID2 only, or ID2 in combination with another index will not using the ID1/ID2 index. So the second index you mention would still get used by queries that need it. The first one, since ID1 is the leading edge of the index and the histogram for ID1/Date and ID1/ID2 will be the same... the second index may be used less. Hard to say again without seeing the queries. I really don't like the ID1/ID2/Date with ID1 & ID2 being nullable. NULL values on your leading edge are going to seriously negatively impact statistics. And stats are the driving force around index use in queries. Plus, index use in queries is not why or how you should be defining primary keys. You should be building appropriate structures. Still sounds like you need an interim table that defines a given relationship and then that acts as the FK to all the other tables.
1 Like 1 ·
If I kept ID1 and ID2 together in one table - option 1 - would the non-unique indexes on ID1 and date for one index and ID2 and date for another index, would those be used as often as I would wish them to be used? and then my unique index would be ID1 and ID2 and date having both ID1 and ID2 as nullable columns
0 Likes 0 ·
OK thanks I will build that interim table, thanks for your help.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.