I am not a developer by any means, but I have a fairly good understanding of how relational databases work and the various types. I am trying to map a database design diagram (using Visio) and need to understand how I would represent the correct relationships in the correct way for a table which needs to reference Parent/Child categories.
As an example: A member/profile table linking to a product parent category table (reference table containing 3 different product types) which also has 2 layers of sub categories for each parent as a tree like structure that drills down into more specific products. I understand the Parent/Child relationships but would I need to link the member/profile table to the Parent Category table (which inherently links to the child categories), or would I need to link the member/profile table to all three tables individually (parent and two child tables).
What I am trying to represent within the diagram, to ensure this is correct is that this database will be used to drive a front end website and when a user selects a Parent Category type from a drop down list, depending on the category chosen, it would then present the appropriate drop down lists for the associated child categories, allowing the specific Product Types and Child categories products to be associated with the member.
I hope this makes sense and I have explained this in a way that someone will understand. I just need to know how to represent this correctly within Visio.
Best Answer
I am going to suggest an alternate solution.
Rather than trying to mock-up a database design in Visio, why don't you create the database design in the relational database? Once that is complete you can use software to create the database diagram from your actual database. Most relational databases have database diagrams built in, and if the built in diagrams don't fit your needs there are a variety of third-party alternatives, many of them free, or with trial periods.
The advantages to this approach is that doing the actual design will help you think through your choices, and even if it doesn't end up being the final design, you can use the work as a starting point.