Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: Primary Key

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: Primary Key

    Please help:

    I am creating a table called Bonus:
    ProductHeading1
    ProductHeading2 (could be null)
    ProductHeading3(could be null)
    Bonus
    Datefrom
    DateTo

    .... what would be the primary key?! I know it would be DateTo and sumfing...... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
    Option 1) heading1 = "X" heading2 = Null heading3 = Null
    Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
    Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"

    ... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to???

    Thanks....

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by STUCK1234
    ... but I need a PK to make sure a bonus is not entered twice...
    not entered twice for what?

    if you pay a $50 bonus, does this mean you will never pay another bonus for $50? of course not!

    so you have to ask yourself how do you identify the separate entities that are getting the bonus

    what are these things, anyway? products? you want to pay a bonus on a product?

    don't the product headings relate to a product table or something?

    can the same three product headings occur again on different dates?

    you need to give some more thought to exactly what this table represents
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    Soz sounds like i aint explained the table properly!!
    headings are product groups...
    eg.

    Heading1 - Heading2 - Heading3- ProductID
    Clothing - T-shirt - Lady - 705921

    Bonus (or Commission) is likely to be a percentage value, the sales team get for the products that fall into that heading....
    eg Clothing might have an overall value of 20%
    while Clothing - T-shirt - Lady might have a specfic value of 10% for 10/10/04 to 10/10/06 and then Clothing - T-shirt - Lady has value of 30% 11/10/06 - 10/10/07....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

    the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

    you should really be putting the bonus on the product group in the product group table

    and if you don't have a product group table, you should
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Sounds like time for a new table:

    ProductID Bonus DateFrom DateTo


    Unless you plan on offering different bonus amounts for the same product id in overlapping data ranges.

    Or do you change the ProductID and have the same product under 2 (or more) ProductID numbers? (I once worked backend for a MAJOR big-box retailer ... that's how their folks handled discounted items ... same thing with different product id ... didn't agree with it, but was in no position to voice my concerns!)

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Feb 2005
    Posts
    76
    [QUOTE=tomh53]Sounds like time for a new table:

    ProductID Bonus DateFrom DateTo


    QUOTE]

    that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30....

    a product can onli be in 1 heading group, no overlapping...

  7. #7
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by r937
    if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

    the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

    you should really be putting the bonus on the product group in the product group table

    and if you don't have a product group table, you should
    yup, its hierarchical...but what would the product group table have:

    GroupID , heading1, heading2, heading3, bonus

    BUT what do I do when the bonus is on

    heading1, heading2... and not the whole Group?!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create table productgroups
    ( id integer not null primary key
    , productgroup varchar(37) not null
    , parent_id integer null
    )

    a typical hierarchical structure, known as the adjacency model

    see Categories and Subcategories for examples of querying this structure

    link each product to whichever product group it belongs to

    product groups at the top of their hierarchy have parent_id null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by r937
    create table productgroups
    ( id integer not null primary key
    , productgroup varchar(37) not null
    , parent_id integer null
    )

    a typical hierarchical structure, known as the adjacency model

    see Categories and Subcategories for examples of querying this structure

    link each product to whichever product group it belongs to

    product groups at the top of their hierarchy have parent_id null

    THANKS...looks promising (yet hard... think there is a lot of data so be a while 2 populate the table.... currently they are just normal columns in my product table!) I'll give it a try and let u no how it goes....

    Thanks agian

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sounds like it's time to normalize the model
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2005
    Posts
    76
    can i not write a insert query something lik this?!:

    INSERT INTO productheadings VALUES
    (SELECT distinct heading1 as name, NULL as parent_id from product)

    basically I want to insert what is in my subquery in2 ma new table?!

  12. #12
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by Brett Kaiser
    sounds like it's time to normalize the model
    Someone else created the database...lucki me... i remba how much i LUVED normalisation at uni

    Thanks

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Stucky,

    Please read the sticky (is he a relation of yours) at the top of this board, and post your question in the terms that it asks for.

    Probably will get a solution in, oh say, 5 minutes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by STUCK1234
    can i not write a insert query something lik this?!:

    INSERT INTO productheadings VALUES
    (SELECT distinct heading1 as name, NULL as parent_id from product)

    basically I want to insert what is in my subquery in2 ma new table?!
    yes, you can write a query likje that

    not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two

    but you are on the right track

    however, something is of great concern -- you have 3 headings in the product table?

    this database is going to require a lot of redesign work...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2005
    Posts
    76
    Quote Originally Posted by Brett Kaiser
    Stucky,

    (is he a relation of yours)
    nope just good minds fink alike

    thanks again

Posting Permissions

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