Database Design: need unique rows + relationships

Say I have the following table:

TABLE: product
============================================================
| product_id | name         | invoice_price | msrp         |
------------------------------------------------------------
| 1          | Widget 1     | 10.00         | 15.00        |
------------------------------------------------------------
| 2          | Widget 2     | 8.00          | 12.00        |
------------------------------------------------------------

In this model, product_id is the PK and is referenced by a number of other tables.

I have a requirement that each row be unique. In the example about, a row is defined to be the name, invoice_price, and msrp columns. (Different tables may have varying definitions of which columns define a "row".)

QUESTIONS:

1. In the example above, should I make nam开发者_运维技巧e, invoice_price, and msrp a composite key to guarantee uniqueness of each row?
2. If the answer to #1 is "yes", this would mean that the current PK, product_id, would not be defined as a key; rather, it would be just an auto-incrementing column. Would that be enough for other tables to use to create relationships to specific rows in the product table?

Note that in some cases, the table may have 10 or more columns that need to be unique. That'll be a lot of columns defining a composite key! Is that a bad thing?

I'm trying to decide if I should try to enforce such uniqueness in the database tier or the application tier. I feel I should do this in the database level, but I am concerned that there may be unintended side effects of using a non-key as a FK or having so many columns define a composite key.

---

When you have a lot of columns that you need to create a unique key across, create your own "key" using the data from the columns as the source. This would mean creating the key in the application layer, but the database would "enforce" the uniqueness. A simple method would be to use the md5 hash of all the sets of data for the record as your unique key. Then you just have a single piece of data you need to use in relations.

md5 is not guaranteed to be unique, but it may be good enough for your needs.

---

First off, your intuition to do it in the DB layer is correct if you can do it easily. This means even if your application logic changes, your DB constraints are still valid, lowering the chance of bugs.

But, are you sure you want uniqueness on that? I could easily see the same widget having different prices, say for sale items or what not.

I would recommend against enforcing uniqueness unless there's a real reason to.

You might have something like this (obvoiusly, don't use * in production code)

# get the lowest price for an item that's currently active
select * 
from product p 
where p.name = "widget 1" # a non-primary index on product.name would be advised
  and p.active
order-by sale_price ascending 
limit 1

---

You can define composite primary keys and also unique indexes. As long as your requirement is met, defining composite unique keys is not a bad design. Clearly, the more columns you add, the slower the process of updating the keys and searching the keys, but if the business requirement needs this, I don't think it is a negative as they have very optimized routines to do these.