SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Logical table setup assistance please

    I'm just looking for some input on the best way to set up a foreign key.

    First, here's my tables:
    company
    products (the main product list)
    myequipment (Instances of each product. FK to company to show ownership. Contains rates, dollar value, FK to product)
    rates (log of equipment rentals. FK to renting company. FK to location_lookup_id to indicate to which jobsite equipment will be located.)
    location (a physical address, a "jobsite" if you will)
    location_lookup (an instance of a location. FK to company to allow name to show up in drop down list per company. FK to location. Name to allow assigning a nickname.
    - id
    - fk_company_id
    - location_id
    - location_name

    Now...my question. At this point, I only have one piece of equipment in the rates table (just for testing). I'm trying to determine what the best way to assign a location to a piece of equipment (product). Now, a product can only be in one place at a time, BUT it would be nice to have a log of where the equipment went during it's time away from it's owner, although that's not super important. What IS important is knowing who has the equipment at any one time. So, I'm wondering if I should just assign a FK in the myequipment table. That way I can indicate where a product is at any time. Since it can only be in one place at a time, this makes sense. However, I'm wondering if a new table might be the best route to take. For example:

    TBLequipment_location
    - fk_location_lookup_id
    - fk_myequipment_id

    Thoughts? Alternate ideas?

    thanks in advance.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SitePoint Member
    Join Date
    May 2004
    Location
    London (sometimes Sheffield), UK
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about location_lookup FK in myequipment rather than a company FK. If location_lookup already has a company FK in it then you will be able to get both the location and the company.

    In terms of the "log" table, if you think people might want that info then do it. Its a lot easier to add it at this stage!!

    HTH

    Dave

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought about leaving it in both in case I need to get just one or the other.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    OK, I'll stick my nose in here....

    I don't quite understand the current structure since it seems complex and the table names don't seem to be descriptive enough for my taste.

    It seems to me you've got three basic tables: company, products and location. I don't understand the location lookup unless you can have more than one site at a physical location.
    Code:
    Company
    -------
     companyid - pk
     companyname
     :
    
    product
    -------
     productid = pk
     productname
     :
    
    location
    --------
     locationid - pk
     locationname
     locationnickname
     :
    You've then got the inventory which shows what each company has to rent
    Code:
    inventory
    ---------
     inventoryid - pk
     companyid - fk to company
     productid - fk to product
     rate
     dollarvalue
     :
    And finally you've got the rentals. I'd use a start and end date to keep track of what's current and what's history. You could have a flag but I'd think that's overkill. If you've got a sub-location site, you'd replace the locationid with the sublocation id.
    Code:
    rental
    ------
     rentalid - pk
     companyid - fk to company
     locationid - fk to location
     inventoryid - fk to inventory
     startdate
     enddate
     :
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave...

    Location_lookup allows multiple companies to use the same address, but give it a different nickname.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes


Bookmarks

Posting Permissions

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