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 |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2008-01-11 : 03:37:43
|
| I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.I want to keep track of which users used a instance of a wheelchair.No there's a developer who likes to put this in one table. (the chair and it's user) in a way like thisUID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumnsThe UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.Is this a good or a common practice? Why use it, or stay away from it?Henri~~~~There's no place like 127.0.0.1 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2008-01-29 : 12:24:02
|
| I'd suggest that wheelchairs and their users are clearly distinct entities with a many-to-many relationship over time. This can be captured by an intersection entity with foreign keys to each of the parent entities plus the date range over which the association exists.Any good data modelling book (or failing that, a Google search) will provide a basic grounding in normalisation, which should highlight the benefits of this approach over the one that your developer is proposing.Mark |
 |
|
|
|
|
|
|
|
Instances and history in one table