Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-03-28 : 03:55:34
|
Say I've got the following:CREATE TABLE postal_codes (i int IDENTITY,code varchar(12) NOT NULL)CREATE TABLE postal_code_distances (i_postal_codes_1 int NOT NULL,i_postal_codes_2 int NOT_NULL,distance smallint NOT NULL) ...as you can see, it's a simple way to precompute the distances between postal (zip, in the US) codes. Now, obviously, I don't want to have two copies of everything, so what I'm doing is ensuring that _1 is smaller than _2 both during the precompute and query phases. So the table distance table looks like:i_postal_codes_1 i_postal_codes_2 distance1 2 351 3 151 4 881 5 652 3 762 4 113 4 813 5 91 ...and so on. App code determines that the distance is 0 for identical postal codes, and for any given lookup it flips the values if necessary so the smaller one is queried against _1 and the larger one against _2.So far so good. It works, it's fast, it's not too unmanageably big. But I'm at a loss for defining foreign keys -- should both _1 and _2 reference the identity from postal_codes? This is probably a simple, stupid question, but it's my first time dealing with a table that has two columns referencing the same column in another table.(And, don't even *start* with the identity issue  . I need to support varchar(12) postal codes today, and quite possibly nvarchar(50) postal codes tomorrow, and with the potential for well over a billion rows, the space and performance savings of doing it with an INT are significant).Thanks in advance-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-28 : 07:07:27
|
quote:
But I'm at a loss for defining foreign keys -- should both _1 and _2 reference the identity from postal_codes? This is probably a simple, stupid question, but it's my first time dealing with a table that has two columns referencing the same column in another table
Oh yeah, that's normal, and it's pretty easy:CREATE TABLE postal_codes_distances(i_postal_codes_1 int NOT NULLCONSTRAINT FK_PostalCode1 REFERENCES postal_codes(i),i_postal_codes_2 int NOT NULL CONSTRAINT FK_PostalCode2 REFERENCES postal_codes(i),distance smallint NOT NULL) |
 |
|
|
|
|
|
|
|
Foreign keys on permutation table?