Table Design/ Structure Help

[SCass2009]

Hi everyone and thanks in advance for any help!!!!

Recently I decided to help a friend set up a database for his small business but at the min I'm having some trouble with the actual table and relationship design with it...

the database is to store customer accounts, as well as details of the various "jobs" offered, in this case pc & laptop repairs, data recovery, website design and search engine optimisation. So far I have the table for storing customer details, as well as a table for storing details on each job (each job has its own table for storing details since details are different for each job).

now this is where I'm stuck - how do I link a customer account to a job

my tables are so far

tblCustomerAccounts
CustomerID
custFirstName
custSurname
custAddressLine1
custAddressLine2
custCityTown
custContactNumber
custMobileNumber
custEmail


tblDataRecoveryJobs
JobID
JobType
Date
Problem
FormatPCPermission
BackupData?
DataToBeBackedup
AdditionalRequests
AdditionalHardwareSoftware
EstimatedJobTimeDays
TotalCost
Quote?
Completed?

tblSEOJobs
JobID
JobType
Date
HoursPerMonth
KeywordIdeas
BusinessDescription
IdealVisitor
CompetitorWebsites
TotalCost
Quote?
Completed?

tblCustomerInvoice
InvoiceID
CustomerID
JobID

But surely having JobID in each table would cause problems with duplicates? And giving each table a unique primary key e.g tblRepairs has RepairJobID, and the invoice table like this:
tblCustomerInvoice
InvoiceID
CustomerID
RepairJobID
DataRecoveryID
WebsiteDevelopmentID

would lead to redundant data as only one job type would be filled?

Any help is greatly appreciated!!!

 

[Scooterbug]

What you want to do is to store the CustomerID with each Job.

 

[SCass2009]

I actually had it like that the first time I tried to sort out the tables and fields but then changed it cause I thought it wouoldn't follow the rules of normalisation i.e. the customerID would be a dupilcate value eventually

 

[Scooterbug]

I actually had it like that the first time I tried to sort out the tables and fields but then changed it cause I thought it wouoldn't follow the rules of normalisation i.e. the customerID would be a dupilcate value eventually

Nope. When the talk about storing duplicate data, they are referring to actual data, not the Primary Key/Foreign Key relationships. Duplicate data would be storing the customer information (Name, address, phone) in both the Customer Table and the Job table. This causes problems because if the Customer's address changes, you would have to change it in both the customer table and the Job table. By referencing the data via the Primary key for the customer in the CustomerTable, you only need to change it in the CustomerTable.

 

[SCass2009]

ah yes I see where your coming from now, I tried it that way first time but thought no, it couldn't be this easy well many thanks anyways, you've prob just saved me from a week of staring blank at the screen!!

 

[Scooterbug]

Glad I could help! Wouldn''t want you staring at the screen too long

 

[gemma-the-husky]

to take what scooterbug is saying a bit further, generally that is what you are trying to achieve in a database.

you split the data into logical tables/entities, so that each data element is stored where it logically belongs. And then you link these tables together logically. doing this efficiently is what is meant be normalisation

where it gets tricky is analysing complex areas -
so in your case, the job, is linked to a customer, and you store the customerid IN the jobs table - so each customer can have many jobs, but each job can only have one customer.

but say a customer brings 3 items in together - you then have to decide how you want to represent this - do you store this as 3 jobs, with 3 different numbers - or do you store it under a single job number, with 3 different items. (probably the former) - but if you do it the second way, then you need a structure that lets you have multiple tasks.

Now when you raise an invoice, do you invoice each job separately - or do you do a consolidated invoice for all the completed jobs (and if you went for the second option above, you NOW have the problem that the whole job may not be complete - just part of it - so does your system let you raise and manage part invoices)

each of these decisions will have a subtle effect on how you need to store the data - there's no arbitrary right or wrong answer, although you do need to end up with correctly normalised data for whichever design option you decided to adopt.

 

[SCass2009]

yeah the plan was just to make it a customer can book one job at a time, if say they wanted 3 things repaired they would have 3 separate jobs for each item, with 3 separate invoices for each item - the user could tick each job off as completed and print the invoice then, think the other would be far to complicated to even attempt lol

 

[Scooterbug]

yeah the plan was just to make it a customer can book one job at a time, if say they wanted 3 things repaired they would have 3 separate jobs for each item, with 3 separate invoices for each item - the user could tick each job off as completed and print the invoice then, think the other would be far to complicated to even attempt lol

Actually, having JobDetails (Each separate repaired item) isn't that difficult. What you would do is have an additional table:

tblJobDetails
JobDetailID (Primary Key)
JobID (Foreign Key)
Quantity
RepairItemName
HoursWorkedOn
RepairBillabeRate
etc, etc, etc

 

[SCass2009]

Managed to get that all sorted out now thanks - hopefully I'll get the rest done without having to beg for help again haha only thing I was looking some advice on is

currently there's a table for storing all components, such as motherboards, CPU, RAM etc. Another table contains various "builds" of computers sold - BuildID being used to identify them, then the various components used to make up the build. So far I'm doing it simply by drop down menus to select the relavent component, for example "CPU" will provide a list of CPUs to select from. Basically -

[FONT=&quot]tblInventory[/FONT][FONT=&quot] (ComponentID,ComponentCategory, ComponentSubCategory, Manufacturer, Model, Cost, Quantity, ReorderLevel, ReorderCost, ComponentDescription)[/FONT]

tblComputerBuilds (BuildID, BuildName, Cost, Motherboard, CPU, RAM, HardDrive1, HardDrive2, OpticalDrive1, OpticalDrive2, GraphicCard, SoundCard)
is this the right way , since tblComputerBuildsis pretty much made up of foreign keys relating to the component selected from the inventory table?

 

[speakers_86]

So far I'm doing it simply by drop down menus to select the relavent component, for example "CPU" will provide a list of CPUs to select from.

Be sure that you are not using the lookup wizard. Instead, use the combo box control. You have this option in the design view of a form.

Basically -

[FONT=&quot]tblInventory[/FONT][FONT=&quot] (ComponentID,ComponentCategory, ComponentSubCategory, Manufacturer, Model, Cost, Quantity, ReorderLevel, ReorderCost, ComponentDescription)[/FONT]

tblComputerBuilds (BuildID, BuildName, Cost, Motherboard, CPU, RAM, HardDrive1, HardDrive2, OpticalDrive1, OpticalDrive2, GraphicCard, SoundCard)
is this the right way , since tblComputerBuildsis pretty much made up of foreign keys relating to the component selected from the inventory table?

It looks good, except that you should not be storing the quantity on hand in your tables. That should be calculated in your queries.

In regards to tblComputerBuilds, I presume these are the parts required for each build. You could do it the way you have listed, but here is perhaps a more proper way:

tblComputerBuilds
ComputerBuildID
ComputerBuildName
Cost

tblComputerBuildsParts
ComputerBuildsPartsID
ComputerbuildID (FK) This is a foreign key linked to the computer builds table.
ComponentID (FK) This is a foreign key linked to the inventory table.


This method allows you to place as many parts as you need in one computer build. If there ever comes a time when a build calls for 20 hard drives, you'll have no problem.