Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2022
    Posts
    6

    Simple database design


    I am building a database to collect information about client visits to a therapy centre. I have a main client table linked to tables with associated contact details, I think I need to set up one to one associations with the main table (specific tables are horse(main), owner, rider, farrier, vet). I also need to set up one to many associations with the therapy tables (Spa, Treadmill, massage). can anyone explain this to me. it is about 20 years since I last used access so have forgotten almost everything I once knew.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would have thought you would need junction tables?
    A client could have more than one horse, use more than one farrier, vet etc?
    You could have a service table that holds Spa, Treadmill, Massages, Pool and any other services you might offer?
    To connect client to that, you would also need a junction table holding keys, date, cost, whatever else you need?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Join Date
    Apr 2022
    Posts
    6
    Thanks for the comment, I am treating the horse as the client so that table becomes the junction to the other tables, with a one to many connection, would the many part of the link be on the horse (junction) table.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well at the least you could have a
    Horses table
    Services table. This would hold farrier, vet services
    Supplier table
    ServiceSupplier table linked to Services and Supplier, so that you can choose who supplied the service, their rates etc
    HorseService junction table kink Horse to Services used.


    Really it all depends on your business to some degree and preference.
    EG: You could store the cost of the service in the HorseService table, by copying from ServiceSupplier table at that time. This means you do not need to look up what the cost was later.
    Or you could have a ServiceCost table that holds the different costs between date periods, and not repeat it.?

    Bit like a Customer, Order and Order items structure.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to an old thread that may help with context and offer some related info. The OP was dealing with Horses and Services. It isn't exactly what you are asking, but has some "similar" pieces. It could be worth a review, and if it doesn't fit, you could reject it from a point of knowledge.

  6. #6
    Join Date
    Apr 2022
    Posts
    6
    Thanks for the information things are slowly becoming clearer, unfortunately the links in the old thread are no longer active so I could not look at the car hire database. however I have looked at the requirements info the ranch owner produced and modified to suit my needs.

    A. What are the Things of Interest?
    A.1 Horses,
    A.2 Owners names
    A.3 Riders names(professional/amateur)
    A.4 Vet names
    A.5 Farrier Names
    A.6 Spa Treatments
    A.7 Treadmill Sessions
    A.8 Salt room
    A.9 linking video and photographs to specific clinical records


    B. How are the Things of Interest Related?

    B.1 contact details of one owner, one rider one vet and one farrier linked to each horse.

    B.2 Record of the medical history for each horse especially injuries

    B.3 being able to link none, one or many of each treatment type to a specific horse

    B.4 There is no invoicing requirement at this stage

    B.5 May need to record which employees carried out each treatment session

    B.6 Would like a front screen or dashboard so end user can input data via forms easily (computer challenged employees)

    B.7 would like to also be able to pull up reports on previous treatment sessions


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

    C.1 Horse details include Full name, stable name, birth year, breed, height, sex,
    clinical history (including date, injury type, injury severity, healing period, and injury details), physiological data (heart rate), links to related humans, clinical records of multiple treatment sessions on a variety of equipment.

    C.2 Contact details include first/last name, billing address, phone number, email, (I started with 4 tables but combined them all into one and added a category box so that each contact could be one or many roles, then prepare 4 queries to identify the individuals with each role)

    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).(possibly for the future)

    C.4 Spa details include date, duration, vibrating floor, air bubbles, water depth.

    C.5 Treadmill details including date, duration, water level cm, water level anatomical, initial speed final speed etc

    C.6 Salt room therapy, date, duration, salt level, lighting

    C.7 We also have a physiotherapist working with us and her treatment sessions should also be recorded.
    ----------



    This where I have got to so far, no links are shown to the 4 queries but the horse name table has combo boxes for owner, rider, vet and farrier

    I have another query called clinical records which used 3 lookup functions for the treatment session, the initial attempt I used ID and date but hid the ID column, this worked ok and produced one record per treatment session, when I tried to change this to show ID (unique number) as well as date it produced 2 records per session.

    Any advice would be welcome


  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 6 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Join Date
    Apr 2022
    Posts
    6
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	51 
Size:	84.1 KB 
ID:	47788Here is a screen shot of where I have got to but I can't resize it

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post your dB?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Steve. Post your database; remove anything private, then post in ZIP format.
    The screenshot you post is not clear.Can you use a similar approach with your B and C lists in post #6.

    Did you understand the basic layout of the tables and relationships in that older post?

    Justin,
    Yes the site referenced in the older post has recently become inaccessible.

    I wa sable to find some of the older material.


    Here is a copy of that Car Hire data model



    And here is the outline of an approach suggested by Barry Williams on the old DatabaseAnswers.org site

    The Approach defined here is aimed at beginners and experienced practitioners.
    It makes some recommendations to simplify basic design decisions on key structures.

    These are the Steps in a Top-Down Approach :-

    1. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
    2. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
    3. Analyze the Things of Interest and identify the corresponding Tables.
    4. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
      For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities.
      If you are just starting out, I suggest that you postpone this level of analysis.
    5. At this point, you can produce a List of Things of Interest.
    6. Establish the relationships between the Tables.
      For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
    7. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
    8. Identify the Static and Reference Data, such as Country Codes or Customer Types.
    9. Obtain a small set of Sample Data,
      e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
      "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
    10. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
      For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
    11. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
    12. You need to define a Primary Key for all Tables.
      For Reference Tables, use the'Code' as the Key, often with only one other field, which is the Description field.
      I recommend that names of Reference Data Tables all start with 'REF_'.
      For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
      This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
      However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
      It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be, Which means, of course, that it often never gets done.
    13. Confirm the first draft of the Database design against the Sample Data.
    14. Review the Business Rules with Users,(if you can find any Users).
    15. Obtain from the Users some representative enquiries for the Database,
      e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
    16. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
      Development staff, etc. and repeat until the final Database design is reached.
    17. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.




    Good luck with your project.
    Last edited by orange; 05-11-2022 at 05:49 PM.

  11. #11
    Join Date
    Apr 2022
    Posts
    6

    equine database

    I have attached the zip file. Names have been changed to initials.

    I will look at the car hire database this afternoon. I think I sort of understood the ranch database, I assumed that the junction tables are the same as queries.
    Attached Files Attached Files

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Justin

    Your 4 tables Owner ,rider, Vet and Farrier Contacts need to be in 1 table.
    In this table named tblContacts you would have a ContactTypeID Field

    I would say that you need a structure that deals with:-

    Many Owners have Many Horses

    To manage this structure you need a Junction Table to link Owners to Horses.

    tblOwners
    -OwnerID - PK - Autonumber
    -OwnerFirstName
    -OwnerSurname
    -(Other fields describing the owner)

    tblHorses
    -HorseID - PK - Autonumber
    -Horsename
    -(Other fields describing the Horse)

    tblOwnerHorses
    -OwnerHorseID - PK - Autonumber
    -OwnerID - FK - LongInteger - (Linked to PK from tblOwners)
    -HorseID - FK - LongInteger - (Linked to PK from tblHorses)
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to mike's suggested tables, you mentioned various Treatments. Each may have distinct attributes. Contacts would include
    Owner, Rider, Vet, Farrier, Physiotherapist and Employee. Your B and C list would be used to define attributes of the proposed tables.
    You may have additional need for Billing/Invoicing which was mentioned in your posts.
    I suggest your work with a model and some test data and scenarios and adjust as needed to match your requirement.
    Click image for larger version. 

Name:	DraftHorseDataModel.png 
Views:	40 
Size:	30.6 KB 
ID:	47791

  14. #14
    Join Date
    Apr 2022
    Posts
    6
    MikeThanks for your comments, I thought I had done this although not entirely as you suggested, I have one contact table which has a contact type box, I have then created 4 queries based on each contact type, so that the vet query only pulls out the vets in the contacts table etc, whilst I was changing the names I noticed that changing names in contacts resulted in the name updating in the query.where I think I have differed from you suggestion is that I have linked my 4 queries to the horse names table So that I haveHorses-HorseID - PK - Autonumber-Horsename-(Other fields describing the Horse)-OwnerID - FK - LongInteger - (Linked to PK from query Owners)-RiderID - FK - LongInteger - (Linked to PK from query riders) etcOne thing I noticed was that once I had changed the contact table to initials the selected names in the horse table did not update with initials although the drop down list for each field did update.However the main function of the database is the relationship between the treatment and the horse, the contact info is of secondary importance

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Sorry Ignore this post
    Attached Thumbnails Attached Thumbnails Dis.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Replies: 2
    Last Post: 11-10-2017, 07:52 PM
  2. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  3. Replies: 4
    Last Post: 12-31-2014, 02:18 PM
  4. Simple(?) Database Design Question
    By mkltmsck in forum Database Design
    Replies: 4
    Last Post: 07-07-2014, 02:00 PM
  5. Database design for simple inventory
    By toptech in forum Database Design
    Replies: 12
    Last Post: 10-24-2009, 07:24 AM

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