December 11, 2008 at 10:42 am
Hi there,
I am designing a DB and is working with bunch of tables. I am unable to draw a clear picture about lookup table and main table. I am seeking advice on how to relate these two tables..
Most of the lookup tables contain year specific codes. Though these codes don't change yearly but as a good practice, I am adding year to the lookup tables which makes design flexible.
Now, main table contains lots of column along with 'code' & 'year'.
I have a PK-FK relationship between 'code' from lookup table to 'code' in Main table.
However, I am unable to decide whether I should have a similar PK-FK relationship between 'year' column in lookup table to 'year' column in main table.
An example to make picture clearer:
LOOKUP
-------
yearlkcodelk
07A
07B
Main
----------
yearseqzonecode
07101A
07202A
07303B
So here, codelk is primary key and code becomes Foreign key.
I am confused about year. Should yearlk be PK and Year be FK. Or Should there be any relation between two?
Suggestions welcome..
Thanks,
December 11, 2008 at 11:59 am
It depends.
Is there going to be a lookup value:
08 A
As well as the value:
07 A
If so, then the year should probably be part of the PK of the lookup table and therefor part of the foreign key on the other table. However, personally, this is a place where I'd probably use an artificial key (it really depends on how much data we're talking about, how often it changes, etc.) with an unique constraint on the two columns in question. This would mean a single column as the primary key on the lookup table and a single column as FK on the other table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 12, 2008 at 9:12 am
Yes, the table will have values like (08,A), (09,A) etc...
sorry but I didn't get the idea of creating unique constraint on Look up table and relating main table with it.
I can think of lookup table with 3 columns..
#,year, code
with # being Primary key having unique constraint on (year,code).
But how to relate it with main table?
I'll really appreciate if u can show sample data for the same
Thanks
December 14, 2008 at 8:02 am
It's a pretty standard lookup table design:
LookupTable
LookupID Year Desc
1 1999 'Some important value'
2 2008 'Some other important value
3 2009 'A yet to be determined important value'
MainTable
MainID LookupID OtherTableValues
1 3 'This matched to 2009'
2 3 'So did this'
3 2 'This went to 2008'
4 3 'back to 2009'
Tables are
CREATE TABLE dbo.LookupTable
(LookupId INT NOT NULL
IDENTITY(1, 1)
,[Year] CHAR(4) NOT NULL
,[Desc] NVARCHAR(50) NOT NULL) ;
CREATE TABLE dbo.MainTable
(MainId INT NOT NULL
IDENTITY(1, 1)
,LookupID INT NOT NULL
,OtherTableValues NVARCHAR(50) NOT NULL) ;
ALTER TABLE dbo.LookupTable ADD CONSTRAINT [PK_LookupTable] PRIMARY KEY CLUSTERED
(
[LookupID] ASC
) ;
ALTER TABLE dbo.MainTable ADD CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[MainID] ASC
) ;
CREATE UNIQUE NONCLUSTERED INDEX [AK_LookupTable] ON dbo.LookupTable
(
[Year] ASC
,[Desc] ASC
) ;
ALTER TABLE dbo.MainTable WITH CHECK ADD CONSTRAINT [FK_LookupTable_MainTable] FOREIGN KEY(LookupID)
REFERENCES dbo.LookupTable (LookupId) ;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2008 at 8:59 am
Thanks for your time and effort. This explains your idea and makes sense to me.
However, I think my problem is bit different. My main table has composite primary key. Though, I can add a unique constraint on columns comprising composite primary key but that would not make much sense to users and wouldn't find use in the environment. Hence, I opted for natural composite primary key.
so, under such conditions, I think it may be a justified approach if I use tables as they are (i.e. no single column PK) and add relationship between yr & code from lookup and main table.
This is my opinion and I would like to know what you think about it.
Thanks
December 15, 2008 at 9:08 am
Depending on the data types in question, there's nothing inherently wrong with a compound natural key. I'm not sure why it would matter to the end user though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2008 at 9:28 am
I think you are correct. I can add unique contraint on main table for columns forming composite PK and create an identity column as PK. Same way, I can work with lookup table and possibly, then can have a proper relationship between lookup and main table.
December 16, 2008 at 12:52 pm
It would appear that you want to have entities that are applicable during a certain time period (a year) and the attributes in lookup tables should only be the values that are applicable during the same time period.
The year is an attribute of the main entity and of the time-sensitive lookup values. Then that year should be part of the PK of the lookup tables and the FK of the main entity table. That ensures referential integrity. The FK can only refer to lookup entries that have the same year as the main entity. If you use surrogate keys, that integrity will have to be enforced through triggers or other means.
Sure, you could create a unique index on the year/code columns and refer to that in the FK, but my question then is "what do you have with a surrogate key that you did not already have?" AFAICS, you'll complicated your physical model and obtain no benefit from it.
Having said that, I think this is a poor design for what you want to do. You have to alter the state of the database at certain intervals (I assume Jan 1 of each year) in order to keep that state current. You think this increases the flexibility but just the opposite is true -- plus you've increased the maintenance effort required to keep the database valid. You want to design the data so that once a value becomes current, it remains current until such time as it is replaced by a new current value.
How do you do that and maintain referential integrity with past versions? That's a good question. I've done something similar before but can't recall the details at the moment. I'll get back to you.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
December 16, 2008 at 12:58 pm
Only if you're assuming that the entire set of lookup values is replaced each year. If the assumption is that the lookup value consists of the value and the year implemented and that it will remain place as such, then the design, while a bit messy and not to my tastes, will work just fine.
On the other hand, if your assumption is correct (and it hadn't occurred to me before) then this design is flawed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2008 at 1:04 pm
no, the lookup values aren't totally replaced each year. Rather, they stay pretty much the same. There can be an addition or deletion from the table.
December 16, 2008 at 1:27 pm
But not deletion when the keys are in use, right?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2008 at 1:37 pm
yeah.
Mostly, its addition. Deletions are very rare. well... i can't recall any delete actions. Actually, we don't remove codes which are not in use.. they are just kept as it is.
For eg. Language Code.
For every country, we have assigned a country code. So, most of times, we get new additions to the table.
December 16, 2008 at 5:20 pm
Then I must admit to being thoroughly confused as to what you are trying to accomplish.
Attached is some code to demo one simplified scheme but now I'm not sure if it will be any help. It allows you to maintain old versions of lookup data for your older entities while using newer versions for new entities.
There is a check constraint on the entity table which makes sure that "future" attributes cannot be assigned to an entity. This will not always be required. You may want to be able to upgrade entity attributes to newer versions -- like exchanging your car radio for a model that did not exist when the car was manufactured.
The triggers (I've separated the Insert and Update triggers for illustration -- they could easily be combined) allow for inserting/updating an entity and assigning an attribute simply by referring to the attribute identifier. The correct date is selected by the trigger. And again, this is just an example and can be modified to fit your requirements.
One advantage is that it allows for creation of a new version of lookup data at any time rather than on an annual basis.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply