Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    HELP - Challenging Data Modeling Problem!

    I have been given the task of creating a fairly well normailized relational database. [Products] is our main table. The other is [Price_Band] and allows a number of name bands to be invented now the difficult bit. I need have the database hold priceband information for every price band for every product. If a new product is invented it should have the [Price_Band] defaults and if a new Price Band is added then all products should have the default settings.

    So how do I create a normal for this mess? I need a working ERD that I can impliment fairly swiftly.

    Any ideas?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    product -----------<- prodpriceband ->----------- priceband

    a typical many-to-many relationship

    the business about new products getting default bands and new bands being added to every product, well, that's all done with application logic


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    So what your saying is that

    For Each [Product] do.sql(For Each [Band] make table)

    or some such...

    Darn

    What would the SQL look like for ensureing no duplicates of a band for a product and checking for the absence of said bands?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Can a Query not be made like:

    Select * from bands unless same exists in exeptions table?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to ensure no duplicates, declare a unique constraint

    in the case of the prodpriceband table, the primary key will suffice, since it is unique

    create table prodpriceband
    ( prodid integer not null
    , pricebandid integer not null
    , primary key (prodid,pricebandid)
    , constraint p_fk foreign key (prodid) references product(id)
    , constraint pb_fk foreign key (pricebandid) references priceband (id)
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Thankyou that might just work...

    I'm not sure how easy this will be as that'll be a first for a primary key I'm useing...

    Thanks again

    -Matt
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  7. #7
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    The real beast is that I have to do this in Access 2000...

    Anyone have thoughts on the combined key?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thoughts on the combined key?

    there can be only one thought: that is the right way to do it

    did you have a specific concern?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Yes, sorry...

    Specific concern: how the heck to talk an MS-Access database into doing that.. if it can be done with one at all.

    Should have made myself clear to start with.

    Thanks for advice it makes a lot of sence.

    EDIT: Hmm I see, I have now created said table... not so hard really. The question that troubles me is how do I treat this table when creating queries?
    Last edited by Matt_T_hat; 01-26-04 at 12:01.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you have access to (no pun intended) the MDB file, just open it, open the table in Design view, highlight both columns, and click on the little key -- this makes them a composite primary key

    if you do not have access to the MDB, you will have to talk to it using DDL -- see Common DDL SQL for the Microsoft Access Database Engine

    as for your other question, "how do I treat this table when creating queries?", i am afraid you will have to be more specific, i don't know what you mean by "treat"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    I think I was worrying too much my friend.

    Thankyou for your help it has been greatly apreciated.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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