0

For my current project, I need to create a database table to store a recursive association. I've done a lot of searching and have read answers on questions, such as Naming convention for associative entity and How do you name your many-to-many relationship tables?, but they don't discuss the case of a recursive relationship.

My company ships out inventory, and they want some parts to automatically include other parts on an order. For example, an order for a Widget will automatically have a Widget Guide added automatically.

The table's schema will basically just be two columns, each a foreign key to the InventoryProduct table. My struggle has been naming this table and these columns. I've considered something like:

InventoryAssociation
--------------------
ParentInventoryProductId : int, FK
ChildInventoryProductId : int, FK

But I don't feel like "InventoryAssociation" is a great name for the table/entity, nor do I think the association between the two inventory products is really a parent/child relationship.

Does anyone have suggestions for better names? Thanks!

3
  • 1
    How about InventoryProductId and AutomaticallyIncludedInventoryProductId? The table could be something like InventoryProductAutomaticInclusions.
    – BenM
    Sep 18 '14 at 21:07
  • 1
    While I think the question has merit, it is asking for an opinion that would be too broad to answer. Why was my question closed as too broad? Sep 19 '14 at 0:26
  • 1
    @AdamZuckerman Heaven forbid we ask for an opinion for naming in programming. You may want to check out the [naming] tag... there's 23 pages of questions you should go close.
    – Nick
    Sep 19 '14 at 15:19
2

There are better patterns for describing hierarchical data structures for example the nested set model. (In your case you probably need a tree identifier because you want to describe multiple trees.)

About the naming. If you are not sure you describe a parent/child relationship, then it is better to describe a graph with nodes and links. In your case the nodes are the InventoryProducts and the links are the relationships between them. So I'd use

InventoryRelationships
--------------------
SourceInventoryProductId : int, FK
TargetInventoryProductId : int, FK

and possibly another fields to describe the type of the relationship. (Btw. a graph database like neo4j would be better if you want to work on huge graphs.) If there is only a single relationship type. Then it should be included by the table name.

Currently I cannot find a better term that the inventory products are related to each other, but I'll check the link relations and some other vocabs, maybe I find a better solution. In the schema.org vocab there is a Product/isAccessoryOrSparePartFor property. So maybe the

InventoryAccessories
--------------------
InventoryProductId : int, FK
AcessoryInventoryProductId : int, FK

or something like that would work for you. If not, then in your case I would try other business related RDF vocabs.

2
  • I'm going to accept your answer since while I don't need anything as complicated as a full graph, I do like your second suggestion. It's given me a few ideas that I'll mull over. And, since my question has been cut off by the infamous Stack Exchange moderation, I probably won't see any other answers anyway. Thanks for your help!
    – Nick
    Sep 19 '14 at 15:17
  • Shit happens... Upvoted. ;-)
    – inf3rno
    Sep 19 '14 at 19:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.