| Author |
Topic |
|
mtlhardcore
Starting Member
1 Post |
 Posted - 2008-01-16 : 13:48:46
|
| Need the following question addressed, as it keeps coming up in our development meetings and has been creating a divide. Pease voice your opinion.To keep it simple, we have Table1 which identifies several questions that are revised on a regular basis. One of it's columns is called "Revision Status". Within revision Status, we would like to identify the possible status of a question such as:New Questions;Revised;Resubmit;Inactive;Active;...as well as several more.I'm of the mind to have these in a seperate table identified with a unique ID... call it StatusTable.Such as:1 New Questions;2 Revised;3 Resubmit;4 Inactive;5 Active;However others feel, just use the "Revision Status" column and simply use the numbers "WITHOUT" a table or description. The developer documentation will tell the developer which number equals the description. ie the following would be found in the Revision Status column.12345My mind says the above is ilogical. I would rather join and say in my statement:WHERE StatusTable.Status = 'Inactive'Where the other way would beWhere [Revision Status] = 4I hope i'm not being thick-headed. Please advise. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-16 : 13:59:33
|
| have a revision status as a number in Table1 so you can easily query it with enums (statuses are usually enums in code)have another table that holds your status id's and descriptions and just have a PK-FK relationship between the two tables._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-16 : 14:02:12
|
| agree with spirit1. definitely seperate table and enforce with foreign key. data integrity is too important to give any serious consideration to those arguing otherwise. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-17 : 04:44:34
|
| Agreed. Another reason IMHO is that a database should be self contained & decoupled from the application. If the application disappeared tomorrow then your database is b0rked - you don't know what the status of any of your questions really is.Doing it like this does not preclude writing 'Where [Revision Status] = 4' either, though I prefer to use the natural key myself for self documenting code, and to protect myself against future recoding. |
|
|
|
JasonL
Starting Member
35 Posts |
Posted - 2008-01-17 : 19:23:38
|
| From a pure database design standpoint a separate table is the way to go. In reality it is sometime better off not to have a separate table (example sex: Male, Female, Unknown) Questions worth asking:If I do not have a separate table: How do one enforced RI? Is it easy to do(this depends on each company/skill)? Is space/storage an issue? How many combinations are there, 5 or 20? Is it going to change?On the safe side use separate table! With Separate table:Pros: flexibility and the correct way!Cons: SQL get complex. This can get "really bad" when you have a lot of these similar "little tables".JasonL (from MSFT)http://blogs.msdn.com/usisvde/ |
|
|
|
JasonL
Starting Member
35 Posts |
Posted - 2008-01-17 : 19:31:38
|
| BTW: To avoid having a lot of little tables we used to have a table called LOOKUP (lookup_type, lookup_value, lookup_description) Kind of like tables within a table and then you create views. example: "REVISION_TYPE", 1, "REVISED""REVISION_TYPE", 2, "SUBMITTED"..."EXPENSE_STATUS", 1, "PENDING APPROVAL""EXPENSE_STATUS", 2, "APPROVED""EXPENSE_STATUS", 3, "REJECTED" etc....of course this method has its pros and cons.JasonL (from MSFT) |
|
|
|
mdgryn
Starting Member
6 Posts |
Posted - 2008-01-21 : 01:33:29
|
| Have a seperate table and write seperate funcitons helps to get the status information and use those functions in your quries this will help you not going away from the effective seperate table solution and also avoids "socalled" deadly joins and effective in implementaion |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-21 : 08:25:30
|
quote:
Originally posted by mdgryn
Have a seperate table and write seperate funcitons helps to get the status information and use those functions in your quries this will help you not going away from the effective seperate table solution and also avoids "socalled" deadly joins and effective in implementaion
Eeek! |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-01-21 : 09:46:05
|
quote:
Originally posted by JasonL
BTW: To avoid having a lot of little tables we used to have a table called LOOKUP (lookup_type, lookup_value, lookup_description) Kind of like tables within a table and then you create views. example: "REVISION_TYPE", 1, "REVISED""REVISION_TYPE", 2, "SUBMITTED"..."EXPENSE_STATUS", 1, "PENDING APPROVAL""EXPENSE_STATUS", 2, "APPROVED""EXPENSE_STATUS", 3, "REJECTED" etc....of course this method has its pros and cons.JasonL (from MSFT)
The problem with EAV tables is that the maintenence can become an absolute nightmare. Indexing these tables properly can become problematic as well.I would say go with the seperate table, especially if it is only a small fairly simple system you are looking at. |
|
|
|
JasonL
Starting Member
35 Posts |
Posted - 2008-02-27 : 18:17:19
|
| Maintaining a lot of little tables (with indexes, space management etc) can also be a pain. I believe either way you have a "problem" somewhere.At the end of the day, question is who should have most of the burden? Developer or the DBA. --- Or more capable of having the burden! JasonL @Microsoft http://blogs.msdn.com/usisvde/ |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-02-27 : 21:31:42
|
| If there is only a little data you can always use a check constraint on the permitted values and no table. In this case I'd use a single or 3 digit char so it at least looks meaningful.I would not have JasonL's idea (Sorry Jason) but you can't enforce integrity using this method. It's not hard to generate lookup tables. |
|
|
|
|
Best Practice Advice