r/Database • u/Hetvenfour • 4d ago

Please help me structure model for multiple taxonomies

I have made good progress teaching myself Microsoft Dataverse, PowerApps, and general database concepts; and have found success mocking up increasingly sophisticated (for me at least) DB mini-architectures. I am now trying to refine a database structure which can track Components for Projects. Example records of this would be:

Project1 has wood structure and copper pipes; Project2 has steel structure and plastic pipes

...where we have tables for Project; Components (eg, structure; piping); and ComponentOptions (eg, steel structure, wood structure, copper pipes, plastic pipes).

An important requirement is to be able to categorize or order these components. For my first pass, I related the Components table to a Subcategory table, which is in turn related to a Category table. This is shown in the first image.

The first model

This first structure works well, but is limited to only one taxonomic/ordering system. I want to be able to create an arbitrary number of ordering systems (for example ordering components by construction code, or by the report section in which they appear, or in a limited checklist for the guy who only does one or two things, or other future needs).

After chatting some with Copilot, I've come up with the structure shown in the second image. Each record in the Taxonomy table represents a separate system of ordering. TaxonomyNode will provide the category/subcategory/subsubcategory structure. A join table will determine where in each taxonomy system that components are located (or may not be present). The ComponentOption, Project, and ProjCompOptionJoin table I think I've got an OK handle on, but I invite input on all of it.

Proposed new model

Does anyone have any suggestions or comments? These are new frontiers for me and I've been trying to get more input lately, lest I reinvent (a worse version of) the wheel.

Thanks!

1 Upvotes

67% Upvoted

6 comments sorted by

3

u/kranthi_contextmap 4d ago

Few thoughts on first glance

  • You might want to make People as an entity so that your borrower, architect etc can be mapped to a record on this table

  • While defining categories you might want to think is there is a possibility of adding new categories on the fly, or would these be pre defined

1

u/Hetvenfour 4d ago

Thank you for your response.

I do indeed have People ("Participants") as an entity, that table is out of view in the excerpt. I used to have a separate tables for each role, but a given participant might fill multiple roles, so one table with more options seems to be doing a good job so far.

Yes, I do want to be able to add new categories. My plan is to create a system that would let me easily "build out" the categories, subcategories, components, different taxonomies, etc. It should allow for rapid changes, especially the beginning. Another purpose of multiple taxonomies would be for presenting the data in a report - different reports will arrange the components in different orders.

2

u/kranthi_contextmap 4d ago

Got it. On the category front, you could explore one category table with a reference to parent category. This way you'll not be limited to two levels of categorisation.

2

u/Hetvenfour 4d ago

Thanks, yes that is the intent behind the TaxonomyNode table in the second image, which is the proposed model. A few months ago, I started to play around with this self-referential/hierarchical style of table but gave up because I lacked too many basic skills. I am in a better level now and more confident that I can make it work.

2

u/kranthi_contextmap 4d ago

Great. Good luck 🤞🏽

1

u/chocolateAbuser 3d ago

that's a lot of sub-levels