Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    DB Design Question - Extra table or same?

    I currently have a table for People and another table to track the assignment for each item to a person. Items can only be assigned to one person at a time so I use begintime and endtime to indicate if the item is still assigned or not. This is all working fine so far...

    What I am trying to do is add Vehicles as a possible assignment "place" and I'm not sure what would be best. The 2 options I am looking at is:

    1. Creating a new table called vehicles and in the assignment table add a field to indicate whether the assignment relates to a person or a vehicle.

    --or--

    2. Add a type field to the persons table (indicating 1=person, 2=vehicle) and adding a new field for vehiclename and vehicles would basically be entered as people but with different attributes filled in and the type field to tell the PHP what fields to look at. (I should probably rename the table if I go this route but thats really the least of my worries right now)

    I'm thinking that route 1 is the preferred but it makes me need to join an extra table to my already multi-left-joined query but maybe thats the best way to go... To simplify this think of a customers table. Do you treat both personal and companies simply as customers with the extra fields and a type to identify the record as a company or do you have separate tables one of personal customers and another as company customers?? that scenario I would contradict myself and choose one single table (route 2)

    Any input is appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    a vehicle can be assigned to a person for a specific period of time
    a person exists in their own right
    A vehicle exists in its own right
    So I suspect you need an intersection table (a third table) which identifies when a specific person had possession of a specific vehicle and everyhtign else associated with that 'possession' of the vehicle eg dates, start mileage and so on)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    No, sorry. Guess I didnt make it clear... An item, in my case a Gun, will be assigned either to a Person, or a Vehicle

    NOT, gun assigned to person who is then assigned to a vehicle.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so you have a three way association table with the gun associated with a person and or a vehicle. its still an intersection table...
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Yes, but it would not be and/or, only OR. Can only be assigned to a person or a vehicle at any one given time.. And the association table already exists called "assignment" but the main question is:

    Scenario 1:
    Person Table
    PersonID | Firstname | Lastname

    Vehicle Table (NEW)
    VehicleID | Vehiclename

    Assignment Table
    Assignmentid | ItemID | PersonID | Starttime | Endtime | VEHICLEID (new)
    In assignment table PersonID and VehicleID would never be filled in in the same record whereby an item will only be assigned to either a person or a vehicle at any one time. PHP would do the verification to make sure that an item is not already assigned to something and if it is it would end that assignment first before entering a new record.

    --- or ----

    Scenario 2:

    Person Table (rename to "location" to encompass both person/vehicles in one table)
    locationid* | PersonFirstname | PersonLastname | VehicleName | Type (1=person 2=vehicle)

    Assignment Table
    assignmentid | ItemID | LocationID*| Starttime | Endtime

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so whether its and / or or just OR doesn't matter. its still an intersection table, just that both person and vehicle attributes in the intersection table can be nullable. your program logic should enforce that
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2008
    Posts
    277
    If you want to be technical about this, look into the topic of sub-typing. You're essentially talking about 2 types of assignments: a person assignment and a vehicle assignment. There are multiple ways to implement this, but the most basic way is what healdem is proposing. Based on the type of assignment, either person_id or vehicle_id will be null while the other will have a value. This should be quite easy to implement in any dbms that supports check constraints.

Posting Permissions

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