Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: A very basic relationship question

    I am creating a BASIC (in relation to what thing you guys do on this website) relational database using access.

    It is for a cleaning business so that they can organise bookings.


    I think I need 4 tables:
    • Clients
    • Bookings
    • Staff
    • ServiceTypes


    linking each of these to the bookings table using primary keys in the first field of each of the other tables will link them and allow me to do relational work (queries etc...) right?


    Somebody told me that I need this though:
    • Clients
    • Bookings
    • Staff
    • ServiceTypes
    • ServiceBookings (this contains the "ServiceTypeID", "BookingID" and "TotalCost" (The ServiceTypes table has hourly cost of each service).



    I don't know why he suggested the second lot of tables instead, can you please clarify?

    Please note: I am a database novice, I have only ever made flat-file databases, this is the first relational I have ever made so please don't be too technical

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You got good advice, although the ServiceTypes table can be expanded to include how many people and hours are necessary for each task, and the hourly charge. The table contains information you will find helpful in quoting pricing (proposals), billing and in planning resources.

    Each client may need differering tasks on a daily, weekly, or monthly cycle: take out all garbage (daily); vacuum the offices (weekly); clean out the fridge (monthly); wash the windows (quarterly); etc. This table can identify a task id (pk), and how many people/hours/$ it needs.

    Your client bookings table can be a fairly static table, containing each client, and how often he wants each task performed as a separate record. This table should not be your master customer table, with addresses and contact information. Make a different table for that.

    The staff table would contain, among other things, the task id (from the ServiceTypes table) s/he is capable of. If s/he is capable of multiple tasks, make each one a separate record, for normalization purposes. This table, of course, would not double as an employee table, with address, phone #, SSN, etc. If you had designed this table solely as an employee table, make a new table for the staff-task assignments.

    I would suggest another table for actuals: x number of people went to client y for z number of hours, totalling $a. You would obtain the '$a' by the program multiplying the various employees/tasks/$ and adding them up.

    Your relationships would be as follows: employee table-->staff assignments (one-to-many); ServiceTypes-->staff assignments (one-to-many); customer table-->bookings (one-to-many). There may be others as well.

    Sam

  3. #3
    Join Date
    Aug 2012
    Posts
    70
    I've had a quick go, am I doing this right?
    (the DB is attached in a .zip file)


    I am having a problem with one of the relationships though:
    Untitled.png Picture
    Attached Files Attached Files

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    After a quick look-over, it might be correct, depending on how you intend the program to run.

    However, I wouldn't check the 'Cascade delete' checkbox in this case, since there are multiple relationships to the same table. Try un-checking it and see if that doesn't clear it up. In truth, I NEVER check the 'Cascade delete' in case I deleted it in error. That way, I can (hopefully) restore the deleted record. I can always delete unattached records manually if I need to.

    Sam

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And a lot of us never delete Records! Instead, we add a Yes/No Field to indicate that the Record is 'inactive.' Then the Queries used behind Forms, Reports, etc. only pull Records where the Field does not equal 'Yes.'

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Aug 2012
    Posts
    70
    I found that the problem was that the ID was set as "Number" in one table and "Text" in the other.



    Is this now correct?
    (See ERD)

    My ERD: http://snag.gy/kJpVj.jpg
    Access ERD: http://snag.gy/drGnN.jpg
    Last edited by kkid; 09-19-12 at 11:04.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That looks about right! Obviously Fields used to link in relationships have to be the same Datatype, with one exception! If the Primary Key is an Autonumber, the Fields linked (the Foreign Keys) must be set as Number Datatype, with the Field Size usually set to Long Integer.

    The way I keep Datatypes straight, when dealing with Fields that only contain Numeric Characters, is this:

    If the Field will never be used in a mathematical operation, I define it as a Text Datatype. Otherwise, I define them as Numbers.

    So things like SSNs, phone numbers, serial numbers, etc., are defined as Text.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •