Home » Developer & Programmer » Designer » Simple Database Design - Any Suggestions?
Simple Database Design - Any Suggestions? [message #416385] |
Sat, 01 August 2009 06:27 |
peace2009
Messages: 11 Registered: July 2009
|
Junior Member |
|
|
I built a sample non real-life database for an automobile agency. The agency have some sellers, cars models, and buyers. I have to keep track of all of them.
These are the process i have taken while designing:
Normalization 1, 2 and 3NF and Relationship one to one and one to many...
Read colored column is Primary Key...
The database not realistic, i did it for practicing purpose. Please look at the schema and tell me do you think its ok so far? or the design is wrong?
Here is my schema:
Seller table stores seller information - a county can have several students while a student can have one country:
Seller Table {SellerID (number), first name (text), last name (text), address (text), city (text), CountryID (foreign key), date hired (date)}
Customer table stores customer information, customer can deal with one seller at a time while a seller can deal with more than one customer at a time:
Customer Table {CustomerID (number), first name (text), last name (text), address (text), city (text), CountryID (foreign key), SellerID (foreign key) }
Car table stores information about cars available:
Car Table {CarID (number), manufacturer (text), model (text), car year (text) }
Purchased car table hold information about customers who purchased a car and type car has been purchased as well as the date of purchase (date purchased relies on both field CarID and CustomerID:
Purchased Car Table {CarID (foreign key), CustomerID (foreign key), date purchased (date)}
OR
Purchased Car Table {PurchasedCarID (number), CarID (foreign key), CustomerID (foreign key), date purchased (date)}
-----
Country Table {CountryID (number), country name (text)}
A customer and a seller can store several phone number:
Phone Table {PhoneID (number), SellerID (foreign key), CustomerID (foreign key), phone number (text)}
|
|
|
|
Re: Simple Database Design - Any Suggestions? [message #416389 is a reply to message #416387] |
Sat, 01 August 2009 06:56 |
peace2009
Messages: 11 Registered: July 2009
|
Junior Member |
|
|
Quote: | a county can have several students while a student can have one country
|
lol, ops...Mistake...
Quote: | Does a Customer have a different phone number when called by Seller one than when he is called by seller two? What is the point of having a Seller associated with a customers telephone number?
|
I want to give a customer a chance to store more than one number e.g. Cell Phone, Home Phone and Work Phone.
I see, seller not necessarily should have more than one phone number. However, if i want a seller to store more than one number, will i have to make a new table that stores sellers phone numbers? Or there is another better way to do it?
Note: i made country table and phone number table since they are considered multi-valued fields. To achieve 1NF, i must let every field in the table (e.g. Seller, and Customer) be atomic.
[Updated on: Sat, 01 August 2009 06:58] Report message to a moderator
|
|
|
Re: Simple Database Design - Any Suggestions? [message #416534 is a reply to message #416389] |
Mon, 03 August 2009 05:01 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I am, as opposed to ThomasG, all in favour of adding an ID column to each and every table. This ID is always the PK and has NO MEANING.
As far as your design goes: I thing the relation between the customer and the seller should be in the sale, not in the customer-table.
|
|
|
Re: Simple Database Design - Any Suggestions? [message #417406 is a reply to message #416385] |
Fri, 07 August 2009 18:29 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
without looking too closely at the design, I think you should consider how some exceptions will be handled. Do you only have buyers & sellers. Can a buyer and be a seller - if so, you can just have a person table (a person can be a legal person i.e. company or whatever too). One way to handle this is 2 different relationships from the "person table". The seller relationship is populated for sellers, buyer relationship for buyers etc. What about if the car is a consignment form a 3rd party (is the seller always the owner).
How to you plan to hold information about the starting stages of a sale? Maybe you've taken a deposit on a car, but the deal isn't done. What about a FK to a trade-in vehicle? Is the new owner always the same as the person who takes delivery of the car? etc. etc....
|
|
|
Goto Forum:
Current Time: Tue Dec 03 15:37:47 CST 2024
|