Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9

    ARGH! Multiple Many-to-Many Relationships!

    Hello all!



    I have been beating my head upon many walls the last week or so trying to figure out what to do for the database design for my small business(a ranch). I have been perusing this forum and many others for the answer(s), but can't quite wrap my head around it!

    In my head, it's simple - I need to manage employee, customer and horse information as well as create invoices for services provided. Here's what I'm pretty sure I have done RIGHT so far:

    tblEmployees
    pkEmployeeID
    FirstName
    LastName
    etc...

    tblCustomers
    pkCustomerID
    FirstName
    LastName
    etc...

    tblHorses
    pkHorseID
    Name
    etc...

    NOW here's where things get complicated for me. I have a multitude of services with different costs:

    tblServices
    pkServiceID
    CostPerRiderPerHour

    ...and then there is the specific record of the customer's current visit:

    tblInvoices
    pkInvoiceNumber
    fkCustomer
    Date
    NoOfRiders
    Time

    BUT what's missing from tblInvoices is Employee, Horse and Service because any customer's visit could have more than one of each! I tried adding three junction tables:

    tblEmployeeInvoice
    fkEmployeeID
    fkInvoiceNumber

    tblHorseInvoice
    fkHorseID
    fkInvoiceNumber

    tblServiceInvoice
    fkServiceID
    fkInvoiceNumber

    ...but it doen't seem to relate properly! One of the end goals would be a form where I or an employee could check off all employees, horses and services (as well as the date, number of riders and the amount of time riding) needed for a costomer's visit. Every time I try to create a query and base a form on it, Access doesn't display the form fields.

    I hope I've been clear on this! If need be, I can attach the mdb (Access 2003) after some info clearing!

    Thanks for your time!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe need an InvoiceDetails table with fields for: InvoiceID (FK), Employee, Service, Horse
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9
    Are you suggesting to add that to the current plethora of tables, or instead of... the junction tables?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Plethora to me always meant "more than you could image; excess", but your proposed tables do not coome close to that (in my view).
    You have identified tables and some structures and even mentioned junction tables. But I don't see any business rules/facts. A database is usually built to support a business. When you evaluate a database structure - tables and relationships - you test that structure against the business rules/facts. It isn't an arbitrary assignment of junction tables; but a business based fact that requires a junction table to record information (and usually it is to resolve a Many to Many construct).

    Do you have a list of requirements, or business facts that we could see? It would be helpful for the readers to understand WHAT your proposed database is about and WHAT your business involves.

    What is involved in the ranch business? Not many of us have experience with ranches.

    As a general approximation, a data model based on a car rental business may give some info.
    I only mention this as a starting point since there are customers,employees "vehicles", bookings/usages....

    There is a data model here
    http://www.databaseanswers.org/data_...hire/index.htm

    and a list of business facts underlying that model here
    http://www.databaseanswers.org/data_...ness_rules.htm

    Here's another data model of Customer and Invoice
    http://www.databaseanswers.org/data_...ices/index.htm

    Note: These models are generic. They are intended to provide some info. You will have to adjust, customize according to your requirements.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    My suggestion was for 1 junction table instead of the 3 you had in mind. With the 3 tables there is no way to identify which employee conducted which service with which horse. The 1 junction table handles that.

    Perhaps issue with your query attempts is use of INNER JOIN.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9

    Business Rules

    Orange et al: Okay, I apologize for not providing enough information and misusing the relative term “plethora” . I will try to follow the structure of the second link posted. First I will give a quick explanation of my business.

    My ranch has 20 horses and 6 employees including myself. We offer 10 different types of services, from riding lessons, to guided trail rides, to boarding of other people’s horses. Our customers call ahead and can take advantage of multiple services with the assistance from many of our staff during one visit. We invoice and they pay before they leave the ranch.

    Okay, now the business rules:
    --------
    Details: The main focus of the business is selling services and renting horses for the services, and the database is to manage the invoicing and use of horses and employees, and give reports on employee hours (for pay), horse hours (to ensure no single horse is being over-used), a horse report for sale, and service reports to determine statistics on services provided. Ability to email updates to all customers or to users of a specific service.


    1. A customer books a time to come to the business sometimes weeks prior, but an invoice is not started until they arrive, as hours and even services often change. When a customer shows up at the ranch, the customer's details are recorded (if not stored already) and a new invoice is made.



    1. When de-worming, shots, or farrier work occurs, multiple (if not all) horse records need to be updated with the date and details of the work.



    1. Employees are paid every 2 weeks, using hours worked and salary information.



    1. Reports need to be created using customers, services and hours for income tax purposes.



    1. When a horse goes for sale, a report of all horse details including de-worming, shots, farrier work and injuries is created.


    A. What are the Things of Interest?
    A.1 Horses
    A.2 Customers
    A.3 Employees
    A.4 Services
    A.5 Hours
    A.6 Invoices
    A.7 De-worming
    A.8 Shots
    A.9 Farrier Work
    A.10 Injuries

    B. How are the Things of Interest Related?

    B.1 A Customer can use one or many Services per Invoice.

    B.2 A Customer can use none, one or many Horses per Service.

    B.3 A Service can use one or many Employees.

    B.4 A Horse can have none, one or many Injuries, De-wormings, Shots, or Farrier Work.

    B.5 Any De-wormings, Shots, or Farrier Work can be done for one or many Horses.

    B.6 A Customer can receive one or many Invoices.

    B.7 An Invoice is for one and only one Customer.

    C. What are the characteristics of the Things of Interest?

    C.1 Horse details include name, birth year, breed, height, sex, whether used for trail rides, lessons, or guide (can be multiple), status of the horse (active, in foal, injured, retired, for sale, deceased), de-worming, shots, farrier (hoof/shoe work) and injury information as well as general notes and hours worked.

    C.2 Customer details include first/last name, billing address, phone number, email, preferred service, preferred saddle and preferred horse.

    C.3 Employee details include first/middle/last name, email, address, phone numbers, birthday, salary, hiring date, spouse name, emergency contact info, photo, notes, and hours worked (for pay).

    C.4 Service details include service type, and cost per rider per hour.

    C.5 Invoice details include invoice number, customer, services used, horses used, date of service, hours per service, and employees used.

    C.6 De-worming, Shots, and Ferrier Work details include type of work done, date, horse(s) involved, and details.

    C.7 Injuries details include horse, date, injury type, injury severity, healing period, and injury details.
    ----------
    That was a good exercise – it helped me realize some more data which needed to be captured!

    I hope this provides a little more clarity for everyone! I really appreciate any help you can give me on this!

    June7: Inner Join may have been overlooked... I'll take a look at that - thanks!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Glad you found it a learning experience. Thanks for getting back.

    Here's another tutorial to lead you to tables and a database. You can try the sample and then try it with your own "facts"; or just use you own facts.

    http://www.rogersaccesslibrary.com/T...lationship.zip
    or the summary http://www.rogersaccesslibrary.com/f..._Databases.zip

    I recommend you try to create the Entity Relationship Diagram.
    Keep us posted on your progress.

    Also:
    You may look at this as a partial adjustment to your How are things Related
    B. How are the Things of Interest Related?

    B.1 A Customer can use one or many Services

    B.2 A Service can use none, one or many Horses

    B.3 A Service can use one or many Employees.

    B.4 A Horse can have none, one or many Injuries, De-wormings, Shots, or Farrier Work.

    B.5 Any De-wormings, Shots, or Farrier Work can be done for one or many Horses. ?????

    B.6 A Customer can receive one or many Invoices.

    B.7 An Invoice is for one and only one Customer.

    B.8 An Invoice is for one or many Services.

    Also, you may consider Injuries, De-wormings, Shots, or Farrier Work to be "HorseMaintenance" with individual items.
    Last edited by orange; 03-07-2013 at 04:27 PM.

  8. #8
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9
    Okay, I've made my way through the Entity-Relationship exercise and I want to run what I’ve figured out by you before I go any further.

    Attached is the Attribute grid and the E-R Diagram. For a minute there I thought that I had a series of one-to-many relationships with the original tables, but when I started trying to put in foreign keys I realized that I was wrong.

    Before I start moving on my database revamp, can you see anything blatantly erroneous in my theory?
    Attached Thumbnails Attached Thumbnails E-R Diagram-v1.jpg   Attribute Grid-v1.jpg  

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Looks good at first glance.
    I tried creating a draft model based on most of what you wrote, but did not have all the info to resolve some things.

    I'm attaching it just because I spent some time creating it. It won't cover all pieces because I wasn't sure what employees worked at if they weren't on trail rides. Also wasn't sure how selling a horse fit in the services etc. I left injuries in "HorseMaintenance". It is meant to give ideas/options, but as I said, doesn't have all the details.

    I did have a NonServiceWork that Employees could be doing if not on service work. Employees could record hours against nonServiceWork. For Service work, ServiceUsed records the ServiceDate, EmployeeId and HoursUsed. My thinking was for each Employee for Pay, you have Hours in NonServiceWork and you can take the services hours by Employee within the PayPeriod to calculate Pay.

    Any way I'm attaching a jpg - there is nothing saying either of these models is correct. You know your business and the things that really go on and what some of the terms really mean in your business. See if there is something blatant , as you are asking.

    I will look at your model in more detail.
    Attached Thumbnails Attached Thumbnails RanchBusiness_5.jpg  
    Last edited by orange; 03-08-2013 at 04:15 PM. Reason: spelling

  10. #10
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9
    Okay, wow - that looks quite impressive! I'm still wrapping my head around it, but I like the non-service work and pay tables!

    A couple questions:

    What is the purpose of the ServiceType table?
    The Horse - Maintenance path is confusing me. I did notice that you had question marks in regards to that previously; I think I didn't clarify it enough. Occasionally, a vet or a Farrier will visit the ranch and administer a service to one or many (often all) of the horses. I want to be able to (in a form) be able to enter the date, select the maintenance (de-worming, shots, or shoes), the vet/farrier's name, and all the horses done (check box), with a check box option for ALL HORSES.

    As for the rest - I need to look at it harder! I'm pretty much off the grid for the weekend, but I will let you know on Monday!

    Thank you so much for your assistance already, Orange. I'm starting to think that I might have something usable at the end of all this!!

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My original thought was something to indicate whether a Service involved a Horse or not. Since you said a Service may include 0,1,many Horses, I wanted something to let me know a Horse could be "optional" for some services.

    The confusion on the de-worming etc was whether or not these could be performed by Employees, or did it involve someone "external" to the ranch. I did allow PerformedBy and a MaintenaceCost, since I felt that would be needed in any paper work related to selling a Horse or other.
    Tbl MaintenanceItems is for identifying uniquely each type of "horse maintenance activity" - de-worming, shots.. and the Description was for any special/additional info beyond a simple name.
    You could have a table of "HorseMaintenanceProviders" (Vets/ferriers..) if there are several, and/or if you need to record any licences etc. (You pick the table name that means that sort of concept in your terms).

    Yes, the checkbox, one horse, all horses, maintenance date etc are all done via a form or forms. Too many people try to put this in the table. Behind the checkbox or button is some vba coding that updates records accordingly.

    I have an Order and Invoice type set up, but you know how you do business and perhaps both aren't needed.

    You may need forms to handle things like change the Employee scheduled for a "trail ride" to another or change the proposed Horse for the ride to another, if the first is injured or already on a "service call".

    I'm not sure how/if you do Bookings --seems more than a draft Order or Invoice. Also, it isn't clear how you would assign Horse and Employees to Customer Services. Could be first come, first served.

    The secret is to get the tables and relationships set up to meet your business facts.

    Then some test data to play stump the model. I say that because that's exactly what we did "in the old days" - put a picture of the model up on a wall; have everyone challenge the model (not the modeller), work it with good and bad data,--Does it do what you expect? If yes, good. If no, then is it the data, is it the model? Reconcile every little anomaly until it passes the test.
    Have a good weekend.

  12. #12
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9
    I am going to try to create a skeleton database based on your image. I need to figure out the flow and I can't quite wrap my head around it on paper. I'll let you know how I do - I'm sure there will be a couple fields I'll need explainations for!
    Thanks again for your continued assistance, Orange!

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sounds like a plan. You may want to look at some of the hotel /car rental reservation system models to see if a Booking table would help in your situation. Models are here
    http://www.databaseanswers.org/data_models/index.htm

    My stuff is mainly a guess at the ServiceUsed detail, but it may give you a starting point.

    Once you get some basic data in the skeleton, create some transactions/test data and write down what you think should happen; then see what actually does happen. As I said before, reconcile every difference.

    Sounds like things are coming together.
    Keep us updated with your progress.
    Good luck.

  14. #14
    Pienuts is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2013
    Posts
    9
    Okay, I have a couple quick questions already:
    How is the relationship made between tblServiceUsed and tblOrderItems using ServiceID?
    Why are we using ServiceDate as the primary key for tblServiceUsed? I could definitely provide more than one service a day.
    Am I right in noticing that tblEmployeeNSWork doesn't have a primary key? And tblHorseMaintenance seems to be using a date as a pk like tblServiceUsed?

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The keys are the item(s) above the line in the EntityBox. The tool I used for this was ErWin. The tool assigns the multiple keys (from the entities that required/necessitated this junction table).

    I normally would have an individual autonumber PK on each table including junction tables. For junction tables I make a composite unique index on the fields that together make a record unique.

    So for your specific questions:

    tblHorseMaintenance could have an autonumber PK HorseMaintenanceId, then as below, a composite unique index on
    ItemId, HorseId and MaintenanceDate since you will want to know What was done to what horse when.

    tblEmployeeNSWork which is really a relationship between Employee, PayPeriod and NSWork.
    Erwin has taken the PKs from each Entity in the Relationship and made them FK in the relationship Entity.

    I would create a EmpNSWorkID as autonumber as PK in your table; Then in the indexes for that table I would create a Composite unique Index of the 3 fields EmpID, NSWorkID and PayPeriodID. What this means is that you have a single PK (easier for use generally) and the Composite unique index prevents duplicates.

    The problem/difficulty with compound PKs is if the Entity involved is in other relationships --gets quite tricky in the subordinate relationships. That's why I would go with the single PK and unique composite index approach.

    I would do a similar thing with ServiceUsed make ServiceUsedID an autonumber PK, then a composite unique index on
    ServiceID, EmpID and ServiceDate.

    I don't have access to a printer and am going back and forth between screens trying to answer.

    More info on composite unique index here (jdraw)
    http://www.access-programmers.co.uk/....php?p=1223790

    When you get a sample skeleton, post it and I will have a look. As I said earlier you still may want to investigate Booking; and I wasn't real sure with the ServiceUsed. But try it and see if it does what you need.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Field Values in Relationships
    By clchris_80 in forum Access
    Replies: 3
    Last Post: 01-21-2013, 01:07 PM
  2. Multiple one to many relationships
    By Iain in forum Database Design
    Replies: 6
    Last Post: 07-01-2012, 03:49 PM
  3. Many to Many Relationships for Multiple Tables?
    By RichNCSU in forum Database Design
    Replies: 13
    Last Post: 05-03-2012, 03:07 PM
  4. How to create multiple table relationships
    By robi212 in forum Access
    Replies: 1
    Last Post: 03-30-2012, 07:59 AM
  5. Displaying Multiple Relationships
    By paddon in forum Reports
    Replies: 7
    Last Post: 12-06-2010, 04:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums