Table Design Help

[Jeff B]

Hi All,

I am working on an exercise for school but am stuck on a particular point in the table design and wondering if someone might help out and clarify for me.

I am tasked with designing a car lot database. This car lot sells new cars, used cars, parts, and services (oil changes etc...) In the criteria it says that the owner wants new cars and used cars split apart.

I am trying to set it up for sales orders and tracking each line item sold on each sales order.

I created 4 tables for the inventory and services: NEW_CARS, USED_CARS, PARTS, and SERVICES
I created a SALES_ORDER table SalesNumber, SalesDate, CustomerID

I am attepmting to make a SALES_ORDER_LINE table to track each line item sold on the sales order but this is where I am stuck and not quite sure how to set this up?

I am Pretty sure I will need to combine the primary key from the SALES_ORDER table and this is the part I do not understand how to do some field from one of the 4 inventory/parts/services table to make the primary key for the SALES_ORDER_LINE table?

Am I on the right track? I think I understand how to do it if the inventory and services were in one table I would just combine the primary key from the sales order table with the primary key from the inventory table to make the primary key for the sales order line table but not sure how to do it with the 4 different tables.

Thanks for the help.

Jeff B.

 

[Jeff Boyce]

A couple comments (see in-line below)

Hi All,

I am working on an exercise for school but am stuck on a particular point in
the table design and wondering if someone might help out and clarify for me.

I am tasked with designing a car lot database. This car lot sells new cars,
used cars, parts, and services (oil changes etc...) In the criteria it says
that the owner wants new cars and used cars split apart.

I am trying to set it up for sales orders and tracking each line item sold
on each sales order.

I created 4 tables for the inventory and services: NEW_CARS, USED_CARS,
PARTS, and SERVICES
I created a SALES_ORDER table SalesNumber, SalesDate, CustomerID
be a CARS table with a field to >>indicate NEW or USED.

I am attepmting to make a SALES_ORDER_LINE table to track each line item
sold on the sales order but this is where I am stuck and not quite sure how
to set this up?
sale (e.g. ORDER) and details (ORDER >>DETAIL).

I am Pretty sure I will need to combine the primary key from the SALES_ORDER
table and this is the part I do not understand how to do some field from one
of the 4 inventory/parts/services table to make the primary key for the
SALES_ORDER_LINE table?
Autonumber (arbitrary) primary key and keep >>(as needed) a unique index on
the others you're considering.

Am I on the right track? I think I understand how to do it if the inventory
and services were in one table I would just combine the primary key from the
sales order table with the primary key from the inventory table to make the
primary key for the sales order line table but not sure how to do it with
the 4 different tables.
template?

Thanks for the help.

Jeff B.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/