Database Design Help

[SCass2009]

First of all, hi to everyone and thanks in advance for any help!!!

problem is this - I'm creating a database/front end for a friend who runs his own computer business. He needs to store customer details as well as job details, ranging from repairs, data recovery, website design and so on. any database work i've done before has had one product or "job", so would each "job" here be stored in its own separate table, linked by another orders table

eg a customer books a data recovery job, all details of the job are stored in tblDataRecoveryJobs

tblCustomerDetails ---> tblJobs ---> tblDataRecoveryJobs
CustID JobID JobTypeID
CustID
JobTypeID

at first I had one giant table for jobs, with details of all types jobs being stored in it, although by going through the normalisation process wouldn't the one big table be broken down into the smaller ones anyways?

sorry if it sounds a bit confusing, any help at all is greatly appreciated

[Hack]

Originally Posted by SCass2009

by going through the normalisation process wouldn't the one big table be broken down into the smaller ones anyways?

That would depend on what is being stored.

For its most basic definiation, normalization means no duplication. In other words, the same information is not store in more than one table.

There would be a job types table with each job type having its own job type code and the job type code would be what is stored in other table records relating to the individual job.

[SCass2009]

so far i have a table for storing the job type, tblJobType, with JobType being the primary key. so for each job type, say data recovery, repairs, website development, I have a table for recording each of the details i.e.

tblDataRecovery
JobID <-- Primary Key
JobType <-- Foreign key
*rest of fields for containing information*

tblRepairs
JobID <-- Primary Key
JobType <-- Foreign key
*rest of fields for containing information*

So when a customer places an order/job, a table tblCustomerJobs would store the CustomerID, JobID and JobType... but isn't this is wrong, since JobID would have duplicates if each job table say started at "1" for JobID?

[Hack]

Ths various IDs are not data...they are pointers to data.

The actual data would be the job description and that should be in only one table, with the corresponding jobid being in whatever tables would be appropriate.

[SCass2009]

sorry, my fault for not explaining what I meant properly

what I mean is say in tblDataRecovery, JobID would be"1" for the first record, then "2" for the next, but then in tblRepairs JobID would also be "1" for the first record, "2" for the second and so on

so if I have a table tblCustomerInvoice, with CustomerID and JobID, how would I do the relationships since the JobID in tblCustomerInvoice would be linking to JobID in several tables

sorry, just most the databases I've done before have been one job or product type, this is the first time I've attempted one with several different jobs!

[Hack]

Don't auto number the JobIDs - assign them.

In other words,

"Build Wall" would have an assigned JobID 0001
"Build Sidewalk" would have an assigned JobID of 0002
etc

The job descriptions in all other tables would simply have 0001 or 0002 or whatever