Database design flows?

[sayedsohail]

Hi everyone,

 

I am trying to create a table design where i can store predifined titles and the other table to store the contents-service logs under the selected titles.

 

table 1 name:titles

Fields:

title_id tiny int autoincrement

title_desc varchar 45 not null

 

 

table 2 name: service

id int autoincrement

car_id int

title_id tiny int ->

service_desc text

date_service date

 

 

In table 2, most of the times i am storing multiple service logs for the same car i.e, if a car comes for a service in a garrage i am storing multiple records under different titles, i.e, oil filter, engine oil.

 

If a car comes for second service, than i am storing multiple records again under service table, which i can identified by the date.

 

I am not sure if this can be improved, any advise would be greatly appreciated.

 

Thanks for reading.

 

 

[Oldiesmann]

I would suggest having a couple additional tables:

 

Customers - A table containing information about all your car owners:

Customer_ID

First_Name

Last_Name

Address

City

State

Zip

Home_Phone

Work_Phone

Other_Phone

 

Cars - A table containing information about all the cars:

Car_ID

Customer_ID

Year

Make

Model

Color

 

Then, modify your logs table like this:

 

ID

Customer_ID

Car_ID

Service_desc

Date_service

 

Or, if you'd like to take it one step further, come up with a list of unique service codes (short text strings that uniquely identify each service) and add a services table:

 

ID_Service

Service_Code

Service_Desc

 

Then you could replace "service_desc" with "Service_code" and have even less info.

 

This will help you minimize the duplicate info stored in particular tables, while increasing flexibility.