Help needed to setting up and relating tables

[MickyB]

Need help setting up tables to track customer details/ orders and cost of service/s provided.

Tables I think I need:

Customer Details Table
CustomerID
FirstName
LastName
CompanyName
HouseName
StreetAddress
Area
County
PostCode
HomePhone
MobilePhone
Email
Notes

Bookings Table
BookingID
CustomerID
BookingDate
BookingTime
Technician
TotalPrice

Job Type Table
JobTypeID (Listed job type for lookup. eg. carpet cleaning or upholstery cleaning)

Job Description Table
JobdescriptionID (Listed job Descriptions for lookup. eg. Living room or 3 seater sofa)
UnitPrice

Technicians Table
TechnicianID (Listed technicians for lookup. eg. mick or john)


Not sure how to create the correct relationships or even if I have used the correct tables and fields. Can someone guide me as to how I create the correct relationships so I can track the jobs of each customer. Please note that customer can order more than one Job per booking. I have no idea how to implement this.

[pbaldy]

You need a booking details table. It would relate one-to-many to the bookings table on the booking ID, and include fields for the job ID, price, and anything else related to each individual job.

[MickyB]

You need a booking details table. It would relate one-to-many to the bookings table on the booking ID, and include fields for the job ID, price, and anything else related to each individual job.

Like this?

Booking Details Table
BookingID
CustomerID
FirstName
LastName
CompanyName
HouseName
StreetAddress
Area
County
PostCode
BookingDate
BookingTime
Technician
JobTypeID
JobDescriptionID
UnitPrice
TotalPrice

[pbaldy]

No, more like this, though I don't know the specifics of your situation:

BookingID (foreign key from bookings table)
JobTypeID
JobdescriptionID
UnitPrice

Maybe technician if more than one are associated with a booking. Typically this table would have its own autonumber key field.

[pbaldy]

Think of it like an invoice, where the header has customer, date, etc, the detail contains a line item for each item purchased.

[MickyB]

How about this? Basically I run a small carpet and upholstery cleaning business and what to build a database to track customers and jobs.

Customer Details Table
CustomerID
FirstName
LastName
CompanyName
HouseName
StreetAddress
Area
County
PostCode
HomePhone
MobilePhone
Email
Notes


Bookings Table
BookingID
CustomerID (foreign key from customer table)
BookingDate
BookingTime
Technician
TotalPrice


BookingDetails
BookingDetailsID
BookingID (foreign key from bookings table)
JobTypeID
JobdescriptionID
UnitPrice


Job Type Table
JobTypeID (Listed job type for lookup. eg. carpet cleaning or upholstery cleaning)


Job Description Table
JobdescriptionID (Listed job Descriptions for lookup. eg. Living room or 3 seater sofa)
UnitPrice

[pbaldy]

I think you're there, though I'd save the booking date and time in one field. You can still present them separately to the user. Your call though, I wouldn't argue against what you have. I'd be more for one field if you dealt with midnight or wanted to calculate duration.

Only one technician goes out on a booking? A case could be made not to store the total in the bookings table, but to calculate it from the line items in the detail table.

[MickyB]

May need abit of help here i think. Have i set them up correctly and the relationships

[MickyB]

NewZip.zip

Here is what I have so far. How do i get the tables to show the unit price of each Job Description?

[orange]

I looked at your database and offer the following for consideration.
As Paul said Date and Time can be stored in 1 field (and separated if needed later).
Each table should have a Primary Key and preferably no spaces in table or field names.
I would suggest TechicianID in Bookings(not Technician)since it is/can be related(FK) to Technicians table.
JobType and JobDescription should not be in the BookingsDetail table. You can get to that info from the
FK JobTypeID and JobDescriptionID in a query when needed.
I would not have TotalPrice in BookingDetails.
I would suggest a field in BookingDetails (eg AgreedToPrice) where you store the actual price you charged for each Detail/Line Item. This is the actual price charged to this customer/this booking on this date. If you change prices for your services, then older invoices/records could change because of that change in price. I included a link to other info on AgreedToPrice for reference. Also, I would ensure referential integrity on the relationships to take advantage of built in validation by the database system.
You may also consider a separate Notes table if you have Notes for each Booking, but I don't know the details of your business.
You may also consider an EstimatedPrice for the Booking when that Booking is created.

Good luck with your project.

[MickyB]

NewZip.zip

I'm really struggling with this. How am I doing?

How do I be able to choose more than 1 JobType and 1 JobDescription per booking?
How do I get the price per JobDescription to input into the Bookings table automatically from its source.

[orange]

I suggest you step back from Access for the moment. Using simple English -no database jargon --describe the pieces and how they fit together just as you would tell a 10 yr old. JobType and JobDescription needs to be expanded so readers can understand your needs in a business context.

We need to now what this really means in your set up.

price per JobDescription to input into the Bookings table

Similarly, this needs some elaboration.

to choose more than 1 JobType and 1 JobDescription per booking

Relationships between tables are not some arbitrary lines. Relationships are an implementation of the "rules or facts" of the business you are attempting to support with this proposed database.

A Booking may have 1 or many BookingDetails
A Customer may make 1 or Many Bookings
etc.etc.

Tables and relationships are the heart of your database. If these are not set up to support your business rules, you will spend a long time tinkering with issues and workarounds. Better to design and test the paper model of these tables and relationships with sample scenarios and outputs to get the proper blueprint to build the database.

Good luck.