August 17, 2018 at 3:35 am
Hi
I've hit a bit of a mental block on my database design
essentially it's a tree view of a company's assets - so naturally hierarchyid is the natural candidate for this...
effectively it's one table with a hierarchy id as the natural key (maybe an artificial key to make things a bit easier) - think of it a bit like a windows folder structure but each node in the tree is a different "node type"
My dilemma is - we have multiple customers using this structure, so do I incorporate them into the node design , or do I make a foreign key to a customers table and have each node in the tree FK'd off to the company?
I suppose I'm asking do I have a forest or lots of little trees with unnecessary labels on each leaf?
MVDBA
August 18, 2018 at 2:51 pm
I think it depends on how you're likely to query it. One option is certainly to have an "owner" id as part of each node, another to have a separate table which links each customer to the root node of their individual tree. The former can make it easier to identify the user from any give folder (since you don't need to interrogate the tree structure), whereas the latter can more easily allow for scenarios such as a tree belonging to a parent company who sees an entire tree with sub-entities each have a child tree beneath that.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply