# Constraint spanning several tables

Imagine the following tables:

Products (Id, Name, ...)

Used for storing products

ProductVersions (Id, Version, ReleasedOn)

Used for storing a version history of a single product (v1.0, v2.0, v3.0, ...)

Customers (Id, ForeName, ...)

Well, yeah. Used for storing customers.

Now a single customer can have multiple products. That requires another table:

CustomerProducts (Id, CustomerId, ???)

What is the best approach here? Should I reference to the Products table (ProductId) or the ProductVersions table (ProductVersionId)?

In terms of joining I'd have access to the Product entity when querying over the ProductVersion of a customer (Customer -> ProductVersion -> Product)

I know it is dependant on my very own design (so if a user owns a product or a version of a product), but what's the best way in general? What if a user can own an entire Product (Product1), but only owns Version 3.0 of Product2?

On product what you have is a Type 2, Slowly Changing Dimension.

A More in-depth discussion on Slowly Changing Dimensions here

A model for this would look something like the following:

Customer stays as is.

Product has a hierarchy (self referencing to the parent [previous version] Product has a different ProductId for each version.

You did not mention whether multiple versions are valid at the same time. Let us assume not.

Valid_From:Valid_To are one way to identify when a product is valid. The 'current' product version has the valid_from set, but valid_to NULL.

When it is time to move to the next 'Version':

1. Update the current records Valid_To date timestamp to now()
2. Get the next ProductId (nextval on a sequence ususally)
3. Get the next Version number (derived from the current version number)
4. Insert new record into Product, valid_From to now(), use new version number, reference the old parentId in the Parent_prodid
5. Commit

You will get new ProductIds for each version, but you will be able to give details of the product/customer relationship over time.

'ownership' of a product may be assigned at any version number. You can cascade the ownership from by maintaining the appropriate CUST_PRODUCT entries, they can be singular (for a specific version) or multiple for all or a subset of the versions.

You can find all versions by cascading thru the self referencing structure until you get the first (oldest, smallest version number, ...)

• What would change if it should be possible to own more than one product at a time? In your post you assumed that'd be not possible, but let's assume it would be. What would change? – SeToY Mar 23 '14 at 15:35

I would probably store product and version in CustomerProduct rather than a surrogate key from the ProductVersion table: that way it is just as easy to lookup both the general product and the specific versioned record when you are starting from the CustomerProduct records.

In fact you might want to use Product+Version as the primary key for that table instead of using a surrogate key and enforcing the uniqueness of Product+Version combinations with an extra unique index (or unique constraint, but both a unique constraint and unique index result in the creation of the same underlying structures).

• Side note : there is still a difference between unique constraint and unique index, and they don't necessary result in creating the same underlying structure. In general case, unique constraint doesn't have to have underlying unique index. – a1ex07 Oct 15 '13 at 14:34
• A unique constraint usually results in an index structure being created (not necessarily always, true, but IIRC MSSQL does create indexes for unique constraints even though it doesn't for foreign key constraints), otherwise enforcing the constraint becomes expensive. I usually chose between the two on the basis of function: if the design means searching/filtering/sorting by the column(s) is likely then I use a unique index (then if the uniqueness requirement changes the index is less likely to be completely removed even though it is desired for searches) otherwise I use a constraint. – David Spillett Oct 15 '13 at 14:59
• For SQLServer yes, but question is not specific to SQLServer. And in my opinion, they could do, and I hope they will do a better job in reusing existing indexes(not necessarily unique) for supporting unique constraints... – a1ex07 Oct 15 '13 at 15:25
• re: SQL Server: yeas, sorry, I was mixing up today's questions/answers there. It is an odd commission that SQL Server won't share structures between an index and a unique constraint. – David Spillett Oct 15 '13 at 16:14

I would probably model the general case as two tables:

CustomerOwnsProduct (Id, CustomerId, ProductId)


and

CustomerOwnsProductVersion (Id, Customerid, ProductVersionId)


Use a view -- where under "Version" you show "All" or the specific version owned -- for presenting combined information -- but be careful to "overrule" (if that's what you want) in case a customer accidentally owns both the product and particular version of the same product.