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!

closed as primarily opinion-based by GlenH7, user40980, Dan Pichelman, ratchet freak, gnat Sep 19 '14 at 9:00

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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? – Adam Zuckerman 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.

  • 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.