Results 1 to 6 of 6

Thread: Prevent different product names for the same ID

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    124

    Unhappy Prevent different product names for the same ID

    Hi,

    This is probably basic, but it is driving me crazy! maybe because I'm still awake since yesterday morning!

    I have a simple table with three fields; "Product ID", "Product NAME" and "Invoice NO".

    ("Product ID" and "Invoice NO") fields combination is a unique primary key.

    In the form I simply enter the three fields' values like this:

    Product ID:1
    Invoice NO:1
    Product NAME: Toy1


    And then I hit Update button and the row gets in the database.

    I then enter the next row like this:

    Product ID:1
    Invoice NO:2
    Product NAME: Toy2

    And then I hit Update button and the row gets in the database without any complaints!

    You clearly see that the product name has been changed. I don't want that! I don't want the database to accept a different product name for the same ID. I can check with a simple code and prevent that but I want the database itself to reject any entry like that. I mean the same way it's rejecting any duplicates in the primary key. is it possible?

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,576

    Re: Prevent different product names for the same ID

    Why did you set the database up the way you did? If there is a reason, then the problem is going to be harder to solve. If there is no good reason, then changing the design is the best solution that I can see.

    I don't see why any table would have Product ID and Invoice NO as being a primary key when Product Name is also in the table. What I would expect is that product ID is the key for the product Name, and that's what you are looking for. Therefore, I would expect that there be one table called something like Products with fields ProductID and ProductName. The PK on that table would be ProductID such that each ProductName had only one ProductID.

    I would then expect a second table called something like Invoice which would have an InvoiceNo as the primary key, and one or more product ID (with no ProductName field at all). Of course, if any one invoice can have only one productID, then that is also possible. Still, the ProductID in the invoice table would just be a foreign key to the Product table.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2013
    Posts
    124

    Re: Prevent different product names for the same ID

    Hello Shaggy Hiker and thank you for your reply.

    Quote Originally Posted by Shaggy Hiker View Post
    If there is no good reason, then changing the design is the best solution that I can see.
    There's no reason at all. I could easily change the design to the one you suggest and it will work perfectly. But I kindly request one clarification regarding this:

    Quote Originally Posted by Shaggy Hiker View Post
    The PK on that table would be ProductID such that each ProductName had only one ProductID.
    Didn't you mean that the PK in the Products table would be a combination of ProductID+ProductNAME? You know, because if the PK is ProductID only then ProductNAME could have more than one ProductID.

    EDIT:
    I mean shouldn't I set ProductNAME to (No duplicates) too?
    Last edited by cPubis; Dec 27th, 2019 at 01:36 PM.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,576

    Re: Prevent different product names for the same ID

    That's hard to say for certain. That would certainly avoid some odd cases, but performance would degrade (though only slightly).

    Generally, something like a Product table is essentially a pick list for use in other places. A person could easily enter the same product name multiple times, and it would get a new product ID each time. That's not good, but the question then becomes, "what's wrong with this?" After all, if there really are multiple products with the same name, then each should have a different ID, but the names are very unfortunate. For example, if you had a product called a widget, and somebody else made a different product and called it a widget, as well, then how you you be able to tell them apart? Only by the product ID, which would be fine for computers, but really really lousy for humans. So, if there really are two different products with the same name, one should probably be given a different name.

    This isn't generally an issue with pic lists, though I currently have a case where the same name is getting added to a list over and over and over (at least, I think I do, it may have been fixed). That's a bug in some software. In most cases, the items in a pick list are all there for a reason, and duplication is avoided more or less by design. However, if you think it likely that a person COULD enter the exact same product over and over, such that it would be the same thing with different product IDs, then you certainly could make a compound PK. The downside to a compound PK is that it's a whole lot wider, which makes queries less efficient. It does solve your problem, it's just up to you to decide whether or not that's really a problem. For example, in the table where I had the same value over and over and over, it had no impact at all, because any query brought back the first one and the rest were ignored. Had I written the query differently, I would have seen all the duplicates. Since that wasn't how it was being used, the fact that there were duplicates had no impact on anything, which allowed several hundred duplicates to be created without anybody noticing, but no other harm resulted.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,762

    Re: Prevent different product names for the same ID

    My two cents is all you need is a unique ProductId. Multiple products could have the same name. Say you have two products, a bag of Marbles with a 10 inside and a bag of Marbles with 50 inside. You would give then different ProductId's but both could have the same Product Name. If you wish you could add the quantity to the product name, I lean towards keeping them separate. Then you can search on the product name and get a list of all the various types and size bags that you have.

    Edit - Well you didn't need my two cent, Shaggy answered while I was typing.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,576

    Re: Prevent different product names for the same ID

    Quote Originally Posted by wes4dbt View Post
    My two cents is all you need is a unique ProductId. Multiple products could have the same name. Say you have two products, a bag of Marbles with a 10 inside and a bag of Marbles with 50 inside. You would give then different ProductId's but both could have the same Product Name. If you wish you could add the quantity to the product name, I lean towards keeping them separate. Then you can search on the product name and get a list of all the various types and size bags that you have.

    Edit - Well you didn't need my two cent, Shaggy answered while I was typing.
    I liked that two cents. It's an interesting question because there's the way it is typically solved (just the Product ID as a key), and there are the issues with the way it is typically solved, which could be solved by using a compound key. There's no RIGHT answer to that one, it's just a matter of trade-offs, and more opinions on that would make that clearer (well, it would make it clear that there's no one right answer, anyways).
    My usual boring signature: Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width