OraFAQ Forum: Designer » Simple Database Design

Home » Developer & Programmer » Designer » Simple Database Design - Any Suggestions?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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)}

Report message to a moderator

Re: Simple Database Design - Any Suggestions? [message #416387 is a reply to message #416385]

Sat, 01 August 2009 06:44

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The phone table looks a little weird.

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?

And the "Country ID" and "Phone ID" seem a little wrong to me. I now, it's somewhat a matter of taste, but when something has a sort of "natural" id then I usually don't introduce a artificial additional ID in the database. The Natural ID for the country would be it's ISO code, the natural ID for the telephone number would be the telephone number. But, as I said, that is more a matter of my personal preference.

And there seems to be a left over "a county can have several students while a student can have one country" from where you took the example. Laughing

Report message to a moderator

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.

Report message to a moderator

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....

Report message to a moderator

Previous Topic:	I need a bit of Help on this ERD project
Next Topic:	EXECUTE_QUERY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 23:58:08 CDT 2024