• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Database Design Question

clamum

clamum

Lifer
I have a question regarding foreign keys and table structure.

Assume I have multiple tables for each type of an object, say vehicles. One for cars, one for trucks, one for personal watercraft, etc.

I also have a customer table that holds customer contact information.

I also have a table, seller, that I would like to have hold a customer id, a vehicle id, a date field (which will be updated periodically), and a hold field that indicates if a customer's vehicle is temporarily on hold.

My problem is that I am not sure how to link the seller table to the vehicle tables. I know how to link the seller table to ONE table's vehicle id field, but not multiple. If that's even possible.

One possible solution would be to combine all of the vehicles into one table, and add a type field to it to indicate the type of vehicle. I am not sure if this is a good design practice or if it does not matter.

FYI I'm using MySQL 4.0.25. I can link a picture to a E-R diagram if that makes it easier to understand. Thanks for any help.
 
clamum,

Don't see why you split vehicle types into separate tables.
One possible solution would be to combine all of the vehicles into one table, and add a type field to it to indicate the type of vehicle.
Click to expand...
Yes that's what I'd do.

So I see 4 tables: vehicles, inventory, customer, seller.

I'd declare a FK in, say Seller, to the PK of Vehicles.
I'd declare a FK in Inventory to the PK of Vehicles.

 
Originally posted by: kamper
Do the various vehicle tables have the same structure?
Click to expand...

Hmmm I forgot to mention that. Yes, out of 21 vehicles types, 20 have the same structure. The one that doesn't has two different fields that the others do not. The type that does not should be able to fit in with the others, I can put "N/A" for a couple of the fields that it does not apply to and it will be OK.

It's probably a dumb question... I had a database class this past semester but it was useless, they ended up switching instructors during the last few weeks of class because the original instructor did not teach a thing and it seemed that he did not know very much about the material. So I've been trying to learn all this on my own at home.

I think scott's recommendation sounds good.

The project I'm working on is a website for vehicle classified ads which range from cars to jet skis to farm equipment. My main concern with deciding on the structure of the tables is satisfying normal forms and making future modification relatively easy.

It's just nice to have people to bounce ideas off. I'm going to make up a diagram to show the structure I have so far and see what you guys think.

EDIT: Link to diagram
 
clamum,

What you want looks like a pretty close adaptation from this template, which will show you a pretty good setup:
by Russell Dyer

In any event, what you're after is really easy, don't feel like you're climbing a difficult mountain of a problem with it. KISS
 
You must log in or register to reply here.
Back
Top