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 |
|
tarana
Starting Member
12 Posts |
 Posted - 2011-08-11 : 05:14:40
|
| I need help with database design. Here's my requirement1. An owner owns several vehicles which will be put to service in different counties. 2. The owner is allocated certain area where only he is licensed, others cannot service this area. 3. In an unlicensed areas, anyone is allowed until it is taken.4. Each county will include certain subdivisions.5. So when a owner is trying to service a certain subdivision, he has first check if he is licensed in that county. If not, is it already taken.Here's my tables (PK means Primary Key)1. Owner - OwnerID(PK) Name, Address2. Vehicle - VehicleID,OwnerID ---PK is VehicleID + OwnerID 3. County - CountyID(PK), OwnerID, VehicleID, CountyName, etc4. AreasLicenced - AreaID(PK), CountyID, OwnerID, AreaName, etc5. AreasSelected - SubdivisionID(PK), Name, OwnerID, VehicleID, CountyID, etc.On the vehicle table, wanted to allow the owners to allocate their own VehicleID not restrict to a unique system-wide ID. Hence the composite primary key.Does this design look logical?Any help & comments are welcome. Wanted to run this design by you all. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-08-11 : 07:35:00
|
With all of the other IDs being system generated, I would generate VehicleID as well. That doesn't prevent the owner from Specifying something like 'VehicleNumber'...Also, just reading the list of reqs, I would probably not used tables 4 & 5. It's not clear, but I think an 'area' is really a 'subdivision' also. So making that assumption I would say table 4 should have been 'Area' and table 5 'AreaLicense'. I don't get the AreasSelected table at all... I'm not sure what you are trying to accomplish with it.In general, I'm not sure why some of the IDs are included so often. For example, why is VehicleID in the County table, why is it in the AreasSelected table? Also, be careful that you don't trap yourself. For example, what if 2 vehicles need to be related to a County, how is that represented?Good luck!Corey I Has Returned!! |
 |
|
|
tarana
Starting Member
12 Posts |
Posted - 2011-08-11 : 18:40:11
|
| AreaLicenced : Several subdivisions where owner has exclusive rights.AreaSelected: Where owner will actually service. Includes subdivisions from AreaLicenced & Area not licensed.As far as repetition of IDs, it just shows my inexpedience in designing DBs. I will look into it & see if any can be removed |
|
|
|
Swaynebell
Starting Member
8 Posts |
Posted - 2011-08-16 : 14:22:35
|
| Hi Tarana,Expanding on Corey's feedback, it sounds like "Areas"/Subdivisions are components of a "County", and it is AREA's that you are working with...so, what about this:OWNER - PK is OwnerIdVEHICLE - PK is VehicleId (OwnerId is a Foreign Key)COUNTY - PK is CountyIdAREA - PK is AreaId, CountyId is a Foreign KeyVEHICLE AREA - an intersection entity with a compound PK of Vehicle Id and AreaId ... this shows the vehicles in each area and you could have multiple vehicles in an unlicensed area. You could add a "IsLicensed" Indicator to show that a vehicle is licensed in a specific area and therefore has exclusive access...I hope this is of some help,Steve |
|
|
|
|
|
|
|
|
Composite Primary key