DB design dilemma - "double" one to one relationship

[nekkev4]

Hi everyone.

I'm working on a database and I've hit a difficulty I can't seem to get passed. Here is the current design:

1. tblPrinter
   1. PrinterID
   2. ArticleID
   3. ModelName
2. tblCartridges
   • CartridgeID
   • ArticleID
3. lkPrinterCartridgeCompatibilities (Link table)
   • PrinterCartridgeCompatibilityID
   • PrinterID
   • CartridgeID
4. Articles
   • ArticleID
   • MakerReferenceCode
   • BrandID
   • CategoryID
5. lkpBrand
   • BrandID
   • Brand
6. lkpCategory (Lookup table)
   • 
     CategoryID
     Category

I need to track a many to may relationship between items within an Articles table.

Basically I have Printers and Cartridges. There is a many to many relationship between them because I need to track printers/cartridges compatibility.

This is just fine. But I then need all those items available as "Articles" in order to be able to link them to an OrderDetails table for instance, or an ArticlePrices table.

So I need one row in the Articles table to indentify one row in the Printers table OR one row in the Cartridges table. A sort of "double" 1 to 1 relationship. This is difficult to enforce.

Based on what I've found on the net + a lot of thinking, here are a few solutions I could find, but all seem to be a kludge:

1. Initially I wondered if GUID could help. I would update periodically printers and cartridges data, with it's GUID primary keys, to the Articles table via an UNION query. This seems pretty unreliable/dangerous though.
2. Have printers have only pair primary keys, and cartridges only odd primary keys.
3. Create a self referential relationship with the Articles themselves (an ArticleID is compatible with another ArticleID). This would be more work to maintain and not as flexible as printers/cartridges M to M relationship.
4. Add a "Category" attribute to the Articles table (category = printer or category = cartridge). Then in the Printers table, constrain the ArticleID selection with an SQL query something like (SELECT ArticleID FROM Articles WHERE Category = 'printers') and same thing in the Cartridges table. This is the best solution I could come up with and it's the one I've implemented in the uploaded zip file.

Is there a better solution to this problem ? I'm not conviced I have found the best solution and any input would be greatly appreciated.

 

[jzwp22]

Welcome to the forum!

. But I then need all those items available as "Articles" in order to be able to link them to an OrderDetails table for instance, or an ArticlePrices table.

You are essentially saying that the printers and cartriges are articles so why not put them in the same table. The rule is that like data should be in the same table.

tblArticles
-pkArticleID primary key, autonumber
-txtModelNumber
-txtModelName
-txtReferenceCode
-fkBrandID foreign key to tblBrands
-fkCategoryID foreign key to tblCategory

tblArticleCompatability
-pkArticleCompID primary key, autonumber
-fkPArticleID foreign key to tblArticles (the primary article i.e. the printer)
-fkSArticleID foreign key to tblArticles (the secondary article i.e. the cartridge)

 

[gemma-the-husky]

but in terms of your problem

if cartridges fit more than 1 printer, and printers can take more than 1 cartridge - then you need a JUNCTION table

table printers
table cartridges
table compatibility (printerid, cartridgeid)

it doesn't matter if printers and cartridges are just products in the same table. the point is you need this extra junction table, to solve the problem

a many-to-many HAS to be decomposed into 2 1-to-many relationships


printer 1 --- many compatiblilty many ----- 1 cartridge

 

[nekkev4]

Re: DB design dilemma - "double" one to one relationship - SOLVED

Yes thanks to both of you.

I have put into a zip folder a very small db with working relations for anyone interested.

So basically in the same way :

• A printer is compatible with many cartridges and a cartridge is compatible with many printers, so
• An article with category "printer" is compatible with many articles with category "cartridge", and vice versa

In that sense it is a many to many relationship, but the other side of the relationship can be seen as a "virtual table" I guess.

In order to input data I use 2 queries and their own subdatasheets, one referencing the printers, and the other referencing the cartridges. This is because a subdatasheet from the Articles table will only give you "one side" of the relation.

That way everything flows both ways beautifully.

Thanks again !

 

[jzwp22]

You're welcome. Good luck with your project.