I have a database with let's say two tables as follows:

Products Table
ProductID | ProductName

and

Widgets Table
WidgetID | ProductID (Foreign Key) | Position


Now how do I set it up if I want a variable number of widgets that attach to a product. But I want to ensure uniqueness in the Position Column WHERE ProductID is XYZ.


So I want to be able to have the following entries in Widgets:

0 | 1 | 1
1 | 1 | 2
2 | 2 | 1
3 | 1 | 3

But not:

0 | 1 | 1
1 | 1 | 2
2 | 1 | 1
3 | 1 | 3

I'm using MySQL but I'm open to other databases. Or do I just need to handle this on the application side to check for a previous entry. I don't mind doing this either, but if there is a way to ensure that no duplicates get entered at all costs would be idea.

Thanks!
Gavin

How are you choosing Position? If that is generated by the server, then it is guaranteed to be unique.

Well how it's actually layed out in my database something sort of like this.

Produt Libraries:

ProductLibrary Table
ProductGUID | ProductName
123 | Product123

ProductAccessoryLibrary Table
AccessoryGUID | ProductGUID | AccessoryName | AccessoryDisplayOrder
85 | 123 | Widget01 | 1
36 | 123 | Widget02 | 2

So when a customer adds 123 (Product123) to their cart they are presented with all of the widget options FROM ProductAccessoryLibrary WHERE ProductGUID = 123

Now they are presented with a list of check boxes.

So in our cart tables we have:

CartProducts:

CartProducts Table
CartProductID | ProductGUID | ProductName
225 | 123 | Product123

CartAccessories Table
CartAccessoryID | CartProductID | AccessoryGUID | Chosen
43 | 225 | 85 | 1
23 | 225 | 36 | 0


And I was thinking I could drop the "Chosen" binary flag if just added Cart Accessories when the checkbox was checked. And then deleted when the checkbox was unchecked. (Since some products could theoretically have 100s of accessories it would save a lot of space.) And of course if the application worked correctly 100% of the time you would never have any orphan accessories since the application should update the cart to the state it was before (with a checkbox checked). And I could add an application call to ensure that when someone checks a checkbox there actually isn't an entry for that CartProductID/AccesorryGUID already. But all of that is dependant on "should work"s.

If I could define in the CART Accessories Table that I only want only one unique entry between CartProductID and AccessoryGUID and it kicked up an error if that rule was ever violated it would be one more line of defense to ensure I don't have any problems. Especially during development when lots of interdependencies might go unnoticed if the application isn't working perfectly.

If I didn't operate on a Drop/Insert tracking system and stuck to a "visible boolean" the worst case scenario would be something like this some how getting created by some software glitch:
CartAccessories Table
CartAccessoryID | CartProductID | AccessoryGUID | Chosen
43 | 225 | 85 | 1
43 | 225 | 85 | 0
23 | 225 | 36 | 0

Then again maybe I'm just being paranoid.