Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Table Relations Q (basic)

    Hi all,

    Am somewhat new to SQL (although have been programming for 2 years), however hopefully someone can assist with a question or two I have (or point me in the right direction resource wise) in creating table relationships.

    1. I have, as an example, the following 4 tables:
    Purchase
    purchaseID (PK)
    Rel: Many to 1 with Budget

    Budget
    purchaseID (PK)
    speciesID (PK/FK)
    Rel: Many to 1 with Species
    Rel: One to Many with Purchase

    Species
    speciesID
    speciesType
    Rel: Has a 1 to Many with Breed
    Rel: Has a 1 to Many with Budget

    Breed
    breedID (PK)
    breedType
    Rel: Has a Many to 1 with Species

    Typically the data in 'Species' and 'Breed' are primarily for reference, and Budget and Purchase are used to record Purchases of Species being of a Breed type. The 'Breed' table is like a subclass of 'Species', with Species Having numerous types with each having numerous Breed types as well.

    Now If I want to store the Breed Type from a selected Species for a Purchase (which is selected from a drop down list directly referencing the Brreed Table) do I:
    (a). Create a field in the 'Purchase' table and simply populate it from the user selection.
    (b). Created a relationship in the DB between the Purchase Table and the Breed Table so as to inherit the breedID value as a Foreign Key which would map, on being queried, the the BreedType.

    (a) doesnt seem right as It feels like I'm replicating data, yet if I applied (b) to all tables in the DB they would all be linked! How far deep in relationship, going through other tables, do you go to retrieve a value before you just relate two tables directly?

    TIA

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    In my view a more natural relationships would be from "Purchase" to "Breed" to "Species", which would also make "Budget" redundant (unless there are some other attributes that you've not shown), because the information could be derived from a join of the other three tables.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    I don't understand the Budget table either. What is its purpose?

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    Sorry, should of explained some more as the Budget table is giving me issues with my design.
    A Purchase details an order by the customer (being of Species type of a particular Breed Type)
    A Budget identifies to the supply company the costs it incurs in supplying (Supply) Species.
    A Budget is not per customer as the act of supplying Species can cover many Purchases, the supply company categorize a Budget per supply on Species type.
    Hence a Supply will have enough Species qty to cover all Purchases. And a Supply will have a Budget per Species type.

  5. #5
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    That explanation made my head hurt. Here is my best guess as to what you need. In this layout Budget really isn't related to Purchase directly, it is only related via species which is determined via Breed:

    Species
    speciesID
    speciesType

    Breed
    breedID (PK)
    speciesID (FK to Species)
    breedType

    Purchase
    purchaseID (PK)
    breedID (FK to Breed)

    Budget
    speciesID (FK to Species)
    CostPer
    BudgetQty

    Thats my best guess. If it isn't close then you will have to organize your thoughts a bit better and try another explanation. Or maybe somebody else here will understand what you have already said.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    unless you have multiple suppliers what does the budget table indicate?
    it looks like a redundant table to me, its not storing anything that couldn't be stored in the species table
    if it contained data from multiple suppliers, or differnt data over time I could see its use, but as described so far it doesn't look right

    in any event I'd expect that sort of infroamtuon to be on the breed level, not species level unless all breeds cost the same.

    take ferisntance dogs
    the cost of dog depends on lots of factors, the physical size, age, quality of pedigree and so on. so I don't think l you could model one budget price for all breeds of dog, and I doubt you will be able to do that for almost any other such species/breed combinations. however I'm sure there are soem, but that may be down to language.
    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
  •