| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2007-08-22 : 23:00:53
|
| I have facing a design problem and unable to justify which design to choose for my data model. Usually, what we have is like data tables and reference tables to store data in those data tables. My database has tables with 20-30 columns in them. And most of them (though not all of them), stores data from some reference tables. Meaning each column has an associated reference table where it stores possible list of values for that particular column. Sort of data domain for that column. FYI, My database is related to medical field.For example, A table that has a varchar(40) column called "Differentiation". It can only store values from following list: - Undifferentiated - Moderate - Poor - Poor - Moderate - Moderate - wellNow, to implement this, simple solution would be to have a reference table where I can store all these possible values...And then have just a reference of each data item into my "Differentiation" column in the table.This is simplest and probably the best solution for such thing and i can also have referential integrity implemented for this.But now if we look at the bigger picture, my database is growing and I have about 80 tables which I need to create where most of the columns will have different reference tables like I mentioned above. Approximate number of reference tables is 300 tables. All the reference table will have same structure, with different values for different columns.Now, what seems to me is, because the table structure is same for every column, rather than having 300 different tables, I can only have 2 tables, where I can put all these reference values into these 2 tables.Like, Table 1 : This table can have name of the reference table like "differentiationlist" etc.Table 2: It has reference to the reference table list in Table 1 discussed above and all the values that are part of that reference table can go in this table with its reference. But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design.Does anyone have any idea or solution for situation like this?Thanks,Ujjaval |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-22 : 23:40:27
|
| What you are proposing is a very bad idea that will cause endless problems. Just create the reference tables and use them.I prefer to use surrogate identity primary keys instead of the actual values with a unique constraint on the actual value column. This allows the joins between tables to be on small integer datatype columns, instead of character strings.CODO ERGO SUM |
 |
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2007-08-23 : 00:08:04
|
| I have thought about it.. But then I am just concerned about the number of tables that my database will have.. Plus, my database has to be compatible with sql server 7.0. I am not sure if it can handle that many tables.But I'll think about other solution, but then I guess I'll create all those reference tables.Ujjaval |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-23 : 07:09:23
|
| You don't have to worry about limits on the number of tables.That is something you could find yourself by looking at SQL Server Books Online documentaion on limits.CODO ERGO SUM |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-23 : 12:27:49
|
| sql server 7.0? you do know that it's not even supported anymore?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-24 : 05:07:55
|
This is called one true lookup table, or oltp. you are not the first think of it. Google around and see if you still fancy the idea.quote:
Originally posted by ujjaval
But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design.
You've hit the nail on the head  |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2007-08-24 : 11:24:19
|
| You may want to consider just using a CHECK constraint, instead of a FK constraint to a reference table, if:1. your main table does not have a lot of rows.2. the reference table only contains a short list with no other information. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-24 : 15:02:44
|
quote:
Originally posted by Ifor
You may want to consider just using a CHECK constraint, instead of a FK constraint to a reference table, if:1. your main table does not have a lot of rows.2. the reference table only contains a short list with no other information.
I would change 1 to:If there is almost no chance of the applicable values changing and that they absolutely would not be configurable by users.The check constraint soution is much quicker & easier than another table & a foreign key so long as you never have to change it. I'm a pessimist so rarely use them for value lists like that. YMMV of course.Actually - having reread your question - please could you provide the names of some (say four) of these "20-30 columns" and some of the values they are permitted? |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2007-09-02 : 21:02:02
|
| I am working on a clinical research database where we need to store surgery information including tumour sites, primary procedure, secondary procedure, dose reduce reason or things like this, where over the period of time, new procedures gets introduced, new reasons come along or they just want to rephrase the reason or combine two surgery name and replace them with one or things like this.I am convinced to use separate reference tables for all the reference data for the columns in my database. Although one last question, Say, I have "dose reduce reason" reference table where I store all the reasons for dose reduction. But these reasons are different for different types of diseases and each disease have their own table where I have this dose reduce reason column. Should I make dose reduce reason table separate for each disease or make a common reference table where all the tables refer to only one reference tables. The only thing is, say dose reduce reasons for each disease are different. So, say in order to put referential integrity I wouldn't know how to implement it if I have only one reference table? I think this question takes me to my basic design that I already implemented with 2 tables for all reference tables without referential integrity.. isn't it?Probably I should use separate reference tables for each disease for their dose reduce reason. What do you think? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-03 : 02:38:27
|
| "But these reasons are different for different types of diseases"Make the PK on the Lookup table be both [dose reduce reason] and the [types of diseases] columns, and Join on both of these for your FK/Refrential integrtity and your Select List etc.Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-03 : 02:39:14
|
| See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Common+Lookup+Tables |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-04 : 08:43:40
|
quote:
Originally posted by ujjaval
...and each disease have their own table...
This is indicative of a design problem. Why do you have each disease in its own table?e4 d5 xd5 Nf6 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 09:08:48
|
| BLindman, not sure if that was the OP making a proposal, or stating what currently exists. I think it can be read either way ... so hopefully not cast-in-stone yet! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-04 : 09:41:04
|
| Better to catch it before the concrete sets, then.e4 d5 xd5 Nf6 |
|
|
|
|
Reference tables or lookup tables