Question about tables

[Unified]

Say I wanted to make a database for a furniture finishing company. One of the tables/sections has to do with doors. I will be storing information about the doors such as color, distress, hours they took to finish, cost to the company, and cost to the customer. For the Doors table, would i put all of those fields on the table, or make a different table for each (Color, cost, distress, etc) and link them to the Doors table? This is going to be a pretty big database, and i will be using the same fields (Color, Cost, Hours, etc) for many different tables besides Doors. Like I will need a table for Doors, Beams, Night Stands, Cabinets, and all of those would need Color/Cost/Hours fields on each table. So if anyone could give me a good idea of how to arrange that, I would be very appreciative.

 

[Mile-O]

STOP!

Okay, that's definitely not the way to go. You need to think about your process and normalise your data.

You'll need a table for colours.

tblColours
ColourID (primary key/autonumber)
Colour (Text)

A table for customers

tblCustomers
CustomerID (primary key/autonumber)
Forename
Surname
Address1
Address2
etc.

tblObjects
ObjectID (primary key/autonumber)
Object (Text - this is where you put door.cabinet, etc. into the field)

tblPricing
PricingID (primary key/autonumber)
ObjectID (foreign key/Number)
ColourID (foreign key/Number)
Price

tblJobs
JobID
CustomerID (foreign key/Number)
PricingID (foreign key/Number)

I don't know, with respect to this, what distress is and I supose you'll need other fields for payments, etc.

The fields over the different tables should be joined together in the Relationships window and Referential Integrity enforced. Cascade delete records should also be selected.

That should help though...

 

[Unified]

Distressing, when talking about wood working, is when you pretty much beat up the wood in a fasion to where it looks like an old or antique piece. There are different styles and names for each type of distress, and it would be nice to be able to store that type of information along with each piece we do. Ok, that did help me out a bit. But, if on my form, I want a dropbox and it to be populated automaticly from the Job # of each piece/job. How would I get that going? Say each door, table, or whatever we do should have a job number. Would I create a Job# table and have Job# be a related field in each table i make? And how do I get the dropbox to auto-populate?
*Edit* Thanks for the help so far.