October 6, 2009 at 6:34 am
Sorry about the garbled subject, I can't figure out the right words to describe the problems.
I am trying to develop a schema which has, I think, 4 tables: Vehicles, Manufacturers, Claims and Dealers.
Vehicles M : 1 Manufacturers
Vehicles 1 : M Claims
Vehicles M : 1 Dealers
Claims M : 1 Dealers
Manufacturers 1 : M Dealers
How do I represent this within a schema so that, for example a Vehicle manufactured by one manufacturer cannot have a claim made against it at a Dealer belonging to a different manufacturer?
My current idea has ManufacturerID as part of a composite primary key in the other 3 tables in order to allow it to be part of the relationships between all tables.
Any help gratefully appreciated!
Martyn.
October 6, 2009 at 7:13 am
There are a lot of things to consider before coming up with the right data model for this... for a start
Do dealers actually belong to a manufacturer... in the UK, the same dealer can sometimes sell/service cars from more than one manufacturer. If so, the relationship between manufacture and dealer may be via the Vehicle.
Who is the owner (parent) of the Claim... is it the dealer or the vehicle, i.e. could the same vehicle be taken to different dealers for repair. If so, you then have a many to many relationship between vehicle and dealer.
Have a look at this site... it has a lot of sample models for various industries, so may give you some ideas. http://www.databaseanswers.org/data_models/
October 6, 2009 at 7:52 am
OK,
A vehicle has one dealer (IssueDealer).
A claim belongs to a vehicle.
A claim has one dealer (RepairDealer).
Does that help more?
Thanks very much for your help!
October 6, 2009 at 8:28 am
A vehicle has one dealer (IssueDealer).
A claim belongs to a vehicle.
A claim has one dealer (RepairDealer).
That sounds like you have circular relationships.
If a claim belongs to a vehicle, and a vehicle has one dealer, then the "claim has one dealer" relationship is superfluous. If you know the claim, you know what vehicle it is for, and you know what dealer the vehicle is with.
Assuming a dealer has a relationship with only one manufacturer, isn't your model (using good old fashioned crows feet to represent a one-to-many relationship):-
|
|
/| Dealer
|
|
/| Vehicle
|
|
/| Claim
October 6, 2009 at 8:50 am
I'm sorry, I didn't quite make myself clear with my last post.
A Vehicle has one dealer which is it's IssueDealer.
However, a Claim also has only one dealer, RepairDealer, but this can be different for every claim that exists, even on the same Vehicle, it is independent of the IssueDealer.
October 7, 2009 at 2:18 am
So you actually have a many to many relationship between a dealer and a vehicle. Therefore, isn't a claim related to the DealerVehicle entity, not the vehicle itself?
|
|
/| Dealer
|
|
/| DealerVehicle--<Claim
\|/
|
|
Vehicle
October 12, 2009 at 11:58 am
Greetings, Martyn;
Excuse the self-promotion, but I think this article will be of use to you. Give it a look, and if you need further help mapping the entities in your domain to those in the example, please post back:
http://www.sqlservercentral.com/articles/Data+Modeling/61528/
TroyK
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply