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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-02-17 : 08:18:46
|
| YellowStreak writes "A couple of questions regarding foreign key integrity.1)I'm reading a book at the mo (Mastering Visual Basic.net Database Programming - Sybex)trying to get up to speed with ado.net.There's some basic DB concepts in the early chapters and I came across this quote:"If you would like to track who created your customer records, you could create a column in your customers table called CreateUserID. This column stores a FK reference to the UserID in the Users Table. However, what if you also wanted to track who updates your customer data? It would be nice to add another FK column, UpdateUserID, that points to the userID in the users table. Unfortunately, SQL Server doesn't allow you to have more than one foreign key relationship between two tables."Is it just me - but that's rubbish isn't it? I've tried it and it works without an error. Am I missing something?2) We have a dispute at work over a DB design:We have a Vehicle bookings system. The bookings table has a foreign Key reference to the vehicle table. Now sometimes a booking can go on the system but the vehicle may be allocated to it later. By text book definition, you should never have foreign key value that is blank (NULL).The only way around this seems to be to add another table between Booking and Vehicle. This seems to me to be over complicating the database design and therefore affecting performance especially as a booking can only ever have one vehicle.The system needs to be scaleable up to a 1000 or so users.My tendency would be just to allow a NULL in the VehicleKey field on Booking until such a time as a vehicle is assigned.My colleague says that this is "nasty",and fundamentally bad DB design and that we have to include the extra table.What are you views, should you always go the text book route or (like de-normalised fields) sometimes it is acceptable to sacrifice perfect world modelling for performance?Thanks in advanceYellowStreak." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-17 : 08:52:53
|
| 1) that's rubbish isn't it?yesAlso if you want to track who updates you need an audit trail table to track all the users that may update not just one.2)Can you book several vehicles in a single booking? In that case you need the conjoint table.Otherwise I would allow null in the field.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-02-17 : 09:54:08
|
| Depending on the vehicle ID datatype you could use a value that will never occur as an ID in the vehicles table. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-17 : 10:09:31
|
| You can put in a dummy Vehicle for those that aren't known yet.rihardhYou can't use a value not in the Vehicle table as it's a foreign key.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-02-17 : 10:17:15
|
| Ups, got carried away by something (overlooked the relationship thing). |
|
|
|
|
|
|
Foreign Keys