Q: Help with recursive tables needed

[John]

1. Overview-
Every Product (tbl_Product) has a single licensor (tbl_Licensor).
Tricky part is, each licensor may have a sublicensor. So, a product
could have a licensor or a sublicensor.

2. Examples-
a. Product: Star Wars
Licensor: Fox
Sublicensor: Lucas
b. Product: X-Men
Licensor: Fox
Sublicensor: (None)

3. Tables (showing only primary keys)-
a. tbl_Product
Product_ID (PK)
b. tbl_Licensor
Licensor_ID (PK)
c. tbl_Product_Licensor
Product_ID (PK, FK)
Licensor_ID (PK, FK)

4. Problem-
I don't know where/how to add a sublicensor in tbl_Product_Licensor
without adding nulls when a product has a licensor but no sublicensor.
Also, should I use a composite key or surrogate key in the table
combining licensor and sublicensor, assuming there is such a table
required.

Any help is certainly appreciated!

John

[Tom van Stiphout]

On 7 Apr 2004 20:25:01 -0700, so*********@hotmail.com (John) wrote:

This does not have to be recursive, if there are no sub-sublicensors.
I would have a tblProducts table with LicensorID (required) and
SubLicensorID (not required), both related to tblLicensors. I'm
assuming here that Fox can sometimes be a sublicensor, and Lucas could
potentially be a licensor.
Yes there will be nulls in the SubLicensorID field. So what?

-Tom.

1. Overview-
Every Product (tbl_Product) has a single licensor (tbl_Licensor).
Tricky part is, each licensor may have a sublicensor. So, a product
could have a licensor or a sublicensor.

2. Examples-
a. Product: Star Wars
Licensor: Fox
Sublicensor: Lucas
b. Product: X-Men
Licensor: Fox
Sublicensor: (None)

3. Tables (showing only primary keys)-
a. tbl_Product
Product_ID (PK)
b. tbl_Licensor
Licensor_ID (PK)
c. tbl_Product_Licensor
Product_ID (PK, FK)
Licensor_ID (PK, FK)

4. Problem-
I don't know where/how to add a sublicensor in tbl_Product_Licensor
without adding nulls when a product has a licensor but no sublicensor.
Also, should I use a composite key or surrogate key in the table
combining licensor and sublicensor, assuming there is such a table
required.

Any help is certainly appreciated!

John