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 |
|
JozzaTheWick
Starting Member
16 Posts |
 Posted - 2004-05-06 : 18:06:18
|
| I'm currently adding some new tables to my database to store two different types of contract - 'fuel' & 'power'. The power contracts have one additional attribute which does not apply to fuel contracts. Previously, in order to avoid nulls in that 'power-only' column, I stored fuel & power contracts into two separate tables, which are identical apart from the extra column in the power table. This created a lot of extra overhead.As part of an enhancement, I am faced with the same decision again. This time I'm thinking of storing both fuel & power contracts in one table, with the power-only column being contrained to zero or null when the 'trade_type' column indicates that the current trade is a fuel trade.The power-only column should then only be populated for power trades, and this would be enforced by the database. How does this sit with normalization and 'best practices'? How have other gurus out there dealt with the same problem?thanks very much.J. |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-05-06 : 18:45:27
|
| If something is optional you may want to use a 1 to 1 relationship.To do this you could make 2 tables. One would be called tbContract. This table would contain columns that both the fuel contracts and power contracts have in common. Then you could make a tbPower table. The tbPower table would contain a FK column that would point to the tbContract table and it would contain the extra attribute that only power contracts contain.When you do your queries you could do a LEFT OUTER JOIN between the tbContract and the tbPower tables. If the tbPower attributes are not null then you know your dealing with a power contract, else if they are null then you know you're dealing with a fuel contract.I hope this helps.Dustin Michaels |
 |
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2004-05-18 : 12:29:16
|
| Dustin - thanks. I got swamped at work and didn't get a chance to get back to this issue! :) |
|
|
|
|
|
|
'Optional' columns in tables?