"Salad" <oi*@vinegar.com> wrote in message
news:tU******************@newsread2.news.pas.earth link.net...
John wrote:
Any suggestions on how to get this to work:
My table includes a index that requires three combined fields to be
unique. However, sometimes the third field will be null....thereby
excluding the record from the uniqueness requirement.
Details:
tbl_Product_Licensor
Product_ID
Licensor_ID
Sublicensor_ID
A product will always have only one licensor, which may or may not
also have a sublicensor. How do I make this three field combo a unique
requirement when one can be null?
Can't see the original post so I'll have to tag this onto Salad's reply.
Question is. What does it all mean? Frequently here people post questions
like this when it appears that the basic design is flawed.
A table in a database is usually the result of defining that purely
theoretical thing a relation which is usually the implementation of an
entity. So what does tbl_Product_Licensor represent. Mmmm....usually a table
with 2 ID columns is a junction table (let's ignore Sublicensor_ID for now)
which is the usual way to implement a many to many join, MEANING, in this
case that many products can have many licensors. But you say that isn't the
case. See what I'm talking about, it's the actual meaning in the problem
domain that you need to get sorted.
So if each product will always have only one licensor, why not:
tlbProduct
ProductID
more product stuff...
LicensorID (foreign key to tblLicensor)
Can a Licensor licence more than one product?
If not then the above isn't correct, and you probably need a one to one. Or
just a single table, including licensor data as well as product data.
If it is many to one, product to licensor, does each product ALWAYS have a
licensor (in which case LicenceID in tblProduct is not allowed null)?
What is a sub_licensor? What do they do? What is their real life
relationship to products and licensors? When we know that we might then have
some idea how to represent them in the database.
Mike