Keeping in mind that performance and scalability are essential to this task, I have this following choice to make in designing a lookup table(s).1) Use a single lookup table in the following formCREATE TABLE tLookups( ckLookupID int not null identity primary key, cRoot int null unique, cChild int null foreign key references tLookups(cRoot), cName nvarchar(256) null, cDescription varchar(4096) null)
Then every table where it needed to use an enumerated type would reference a certain set of child elements.The second solution is having a table for every enumerated type... since some enumerated types can be modified by the users i need to make sure that the application will perform well... my concern with the first option is that I'll have close to 250,000 records in that table and that selecting a set of child records would take quite some time... the other concern I have is that if I don't use the lookup table then i'll have about 50-80 extra tables that mainly just sit and get read... there won't be alot of writing data just a lot of reads...any suggestions on ways to break it down better or other implementations....Regards,AdamEdited by - onamuji on 03/23/2001 10:02:26
Recommendations for large lookup tables...
Posted - 2001-03-23 : 10:01:47