Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2012
    Posts
    14

    Question Unanswered: Help with Relationship Question

    I have a table with visit date, provider, and date of birth. The primary key is VId and this is the foreign key in the other related tables. All works fine now.

    I need to add a table which has the following a date field, which can be any date in the month, hours, and hours with days off.

    I am having difficulty coming up with a relationship which takes the information from the visit table and other related tables and links it to the new table.

    Can someone pleas help me with this.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the entity you are trying to model, what doe sit represent.
    its absolutely impossible to give a helpfull answer on the infroamtion you have provided.

    I suspect you need to have a clearer idea in your own mind as to what you are trying to represent and the PK & FK's will 'pop' out from that
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    14
    The visit table represents a one time event, who provided the service, and the visitor's date of birth.

    This is then linked by the VId to all the other tables from which reports are generated from query functions.

    I now need to add the table mentioned to gather utilization of time.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Some issues here

    What does the visitor's DoB have to do with the visit? More to the point, what purpose does it serve in recording this each time? Do you know people whose DoB changes on a regular basis?

    If a visit is a one-time event, why do you need to add another table to capture its duration? Add fields to capture the start date/time and end date/time of the visit, and you can derive the duration from them in queries, reports and forms.

    As healdem said, you have not provided a great deal of information. All we know about your database is what you tell us...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2012
    Posts
    14
    Each visit is a single event. Date, DOB, and Provider are unique for the event and have a VID (primary key).
    The primary key is then the foreign key in the other tables of which one holds a start and finish time.
    What I am attempting to do is add a table that will hold available time, time take off, etc., for each provider and have that be linked to a date range which is queried off the visit date.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I can't help thinking that your basic design is a little off key.

    Visit Provider's DoB is nothing to do with the Visit, so should not be stored in that table. You need a Provider entity that will hold this.

    Similarly, the start and end times of a Visit are very much something to do with that Visit, and so should be accessible ONLY via that Visit's primary key. I cannot see a logical reason for splitting them out into other entities (although I'm sure that some of the other users on this forum will be able to cite real world examples of where this is a good idea).

    While I can see the initial attraction of a table to hold the available time for Providers, this will be a lot of work. Also, in order to calculate how much of their time is available after existing visits are accounted for, you need to their working hours - something that will need to be linked to a Provider ID and day of the week/month (depending on how things work). A query would be a better way to go, as it will always show the current result, without having to read source data, and then clear down and repopulate another table.

    If you want to post a copy of your database with sample data, I might be able to make more tailored suggestions.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    logically a visit is the intersection of a provider and a consumer
    how you identify that provider and consumer is is down to you, but a date of birth isn't sufficiently unique in my books, even if you were to set date of birth to include the time of birth as well.

    if your system is passive (recording after the fact then a datetime value) may have some value but if its pre actual visit (ie person 4 arranges to meet provider Z at some time in the future ie a booking) then it becomes a problem as that booking may be changed. something you do not want to do with a primary key.

    for this sort of entity I'd probably use a an autonumber primary key and enforce uniqueness onby a unqiue index
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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