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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-08-14 : 12:30:16
|
| Hi, I'm writing a application where some of the dropdown lists need to change depending on a certain criteria. For example the list of driving violations changes on a state by state basis. I see two possible solutions.1) Add an attribute "state" column to the violation table. This state column can contain the code for each state and a default "All" value. When querying for the available lists add "Where State in ('WA','All')" clause.Pros: all dropdown list data stored in one table, easy to query.Cons: primary key is now a combination of violationType and state (can't add contstraints etc). CREATE TABLE Violations ( [ViolationType] [varchar] (10), [State] varchar(3), [Description] [varchar] (30), [Visible] [bit], [SortOrder] [int])2)Add an additional ViolatonStates table. The Violations table contains a master list of violations and the violationsStates table contains which state has which violation.Pros: primary key is still ViolationTypeCons: Data stored in two tables, more difficult to query, what happens if two states have the same violation but want different descriptions? Then have to have two ViolationTypes that mean the same thing. All attributes (sortOrder, visible) of the dropdown list have to be moved to the ViolationStates table. What happens if in the future there is an additional attribute that controls display, do I create another table?I'm pretty sure I am going to go with the first approach. It seems cleaner etc. My only concern is when storing the ViolationType in the Driver table (very very simple example: users select a violation (from a list) for a driver, I store the ViolationType in the driverViolations table.) Since the primary key of violations is a combination of violationType and state, to get the proper Description etc I need to get the driver's state.I like the idea of not having distinct violationsTypes for all states since speeding is speeding (no matter what state you are in). I could add an additional identity column to the violations table and store that value in the drivers table but then once again I am making a distinction between items that are the same but just in different states. Is storing the violationType in the drivers table ok, even if it is only part of the composite key?Does this approach sound good?ThanksNic |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-15 : 07:47:56
|
I think your should concern yourself less with concepts like 'cleaner' and 'difficult to query' and more with the concept of 'normalization'.I think the most important thing you said was that different states may have different descriptions for the same violation. That says to me that description is functionally depenedent on {violationtype, state}, therefore option 1 does not contain any transitive or parital dependencies and there are no determinants that are not candidate keys for the table. Thus, by your business rules, is in (at least) 3NF/BCNF. That would be my choice. (I don't believe this table is a candidate for 4NF or 5NF discussion since there is only a single multi-valued fact in question....so I suppose you could say it's in 5NF, really.)quote:
Cons: primary key is now a combination of violationType and state (can't add contstraints etc).
I'm not sure what you mean by this? Give an example of a constraint you can't have because of the multi-column primary key. I think you are refering to your concerns about the driver table. If that is true, the answer is simple: store the entire foreign key on the driver table {statecode, violation}.I think I understand what feels uncomfortable for you. Consider a violation like 'DUI'. Pretend that in all states, except Georgia, DUI is a violation. Because of that, instead of having one row {'All','DUI','.08% BAC or higher'} you will need 49 rows {StateCode,'DUI','.08% BAC or higher'}. Now lets also say that in Rhode Island, they cap the BAC at .05%. You end up with 49 rows, 48 of which have the same description and 1 has a different description.Considering that, one would be tempted to say, "Well, that looks like an update anomoly nightmare waiting to happen. I'm going to create a violation-description table {descipID int identity PK, description varchar}. Then I'll have my violation table as {statecode char(2), violation varchar, descripID int, PK(statecode,violation)}."On the logical layer, this makes no sense. Logically, descripID does not exist, it is contrived, a surrogate, it is intangible. Because of this it cannot have attributes and cannot be an entity on its own.However, on the physical layer, there may be some value to this physical schema with regard to performance. Having a narrower row on your violations table (meaning having the int descripID rather than the varchar(big) description) will save disk space and may allow you more rows per page. More rows per page means quicker scans/seeks. However, that must be traded off with the effort need to hash to the violation-description table every time you want to paint your drop down box. The only way to know is to implement both schemas and create a suite of tests to identify how each performs on different DML actions in the proper ratio to your expected load.However, don't be fooled into thinking that the violation-description table will releave your update anomoly stress. You will still carry that ID for each state-violation pair and you can still botch up that mapping. Note: that's my main beef with IDENTITY. It gives a false sense of DRI.Jay White{0} |
 |
|
|
|
|
|
|
|
Design questions (dropdown lists)