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