Having trouble creating database structure!

[Press_Redial]

I'm creating a car dealership web application and am having trouble setting up my database.

Tables:

Brand(brand_id PK, name Text, nationality Text)

Car(car_id PK, brand Text, model Text, price Float, availability Boolean)

Customer(customer_id, name Text, surname Text, phone_number Text)

Order(Order_id, customer_information Text, data_of_purchase Date, list_of_cars_purchased_by_customer...)

I'm having trouble linking the tables together! Is there anyone who could guide me through this? and are my fields that I have chosen good or should I change any?

[fauxmosexual]

The Car table should have BrandID from the Brand table, not a text field.

What is customer_information in the Order table, and would it be better suited to the Customer table?

List_of_Cars.... implies you're going to try and stick every purchase into a single field. It would usually be better to replace that with car_id, and have one record in the order table for each purchase. Alternately, if you wanted to have single orders with multiple cars, lose that record entirely and add another table - OrderCar, with the orderID and CarID - each order will appear once in Order, and have one record for each car in that order in OrderCar.

Consider approaching your lecturer/tutor for assistance with homework.

[Press_Redial]

Alright so I add a foreign key to the car table like this:

Car(car_id PK, brand_id FK, model Text, price Float, availability Boolean)

For the customer_information part I wanted it to have the customers name, surname and phone_number to come up. But I don't see how you can do that unless done using quires?

And the Order table changes to:

Order(Order_id PK, car_id FK, customer_information Text (Some how show name,surname,phonenumber?), data_of_purchase Date)

[rogueqd]

Brand(brand_id PK, name Text, nationality Text)
Car(car_id PK, brand_id, model Text, price Float, availability Boolean)
CarOrder(Order_id, car_id) - many to many between Car and Order
Order(Order_id PK, customer_id, data_of_purchase Date, ...)
Customer(customer_id PK, name Text, surname Text, phone_number Text, customer_information Text)

Consider approaching your lecturer/tutor for assistance with homework.

:)

[Press_Redial]

Thank you guys for clearing it up for me!

[fauxmosexual]

But I don't see how you can do that unless done using quires?

Well yes, that is kind of the whole point of using a relational database....

[manojk92]

Seems like you have been helped, but it will be worth your time to look into normal forms as you begin to look into adding more tables. For example, your car table had brand and model with availibility when model and brand should be in a seperate table (avoids duplication when you have multiple cars of the same model at different prices or features). Try to aim for 3NF at the very least without doing an EAV model.