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-03-04 : 14:38:12
|
| Hi,I just want to confirm this database schema follows proper normalization rules. We have a UserViolations table that stores a list of violations for a user (ie. speeding tickets, robbery, parking tickets). All violations have a certain set of fields we need to track (ConvictionDate, violation id). Some violations have additional info we need to store (ie. for speeding violations we need to store the mph over the speed limit). That being said we have a main UserViolations table and a few other tables that store the additional info needed for special violations. This way we don't have a MPH column in the violations table which is null 90% of the time. Is the proper way to design the database? Is there a good rule of thumb for when data should be stored in a seperate table or just in the main table? In other words, how do you decide if you just add a column or create a new table? CREATE TABLE UserViolations ( UserViolationID int, UserID int, ViolationID int, IncidentDate datetime, ConvictionDate datetime )CREATE TABLE UserViolationLoss ( UserViolationID int, LossType varchar(10), TotalDamage int, DeductPaid int, BodilyInjury bit )CREATE TABLE UserViolationMPH ( UserViolationID int, MPHOverLimit int )Nic |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-03-04 : 14:49:20
|
| I'm not sure on how "normalized" this is, but here's another idea.Have a "Violation Parameters" table that store Key/Value pairs for a violation.Another solution might be 2 tables, ViolationType and ViolationParameters. Set it up in such as way as each Type has certain parameters. A Violation has a type which has the parameters.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-04 : 15:08:04
|
| I think this is a good design. Store all common elements for every violation in the master table, and then use "sub-tables" for details when the violation needs it.You can always LEFT OUTER JOIN them all together into 1 view if you ever need to see all data in 1 table.Michael's idea is good as well and very flexible, but can cause problems if the values are of different datatypes, and also you may find you need to do a lot of cross-tabbing to get the data into traditional row/column format for reporting.- JeffEdited by - jsmith8858 on 03/04/2003 15:09:32 |
|
|
|
|
|
|
|
|
proper design