CODEWITHSUNDEEP
database-designrelational-database
Farhad-Taran
Farhad-Taran

Reputation: 6512

How to choose the right storage technology and design for a tree like date structure that requires fast reads

I have a requirement to design a tree like data structure whereby child entities can inherit or override values from parent entities.

If you could imagine three above entities with client being at level 0, entity at level 1 and channel at level 2. The entity level items can inherit or override attributes from the client level and channels can inherit or override attributes from their entity level parents.

I also need the channel to be sub-sectioned into groups where each channel can be valid from a specific dateTime. So if I make a query to get a specific channel I will pass current instance of time and I will need to get back the top first item which is less than the input datetime parameter.

Hopefully the tree's depth will be set at 3 and there won't be any extensions. My question is what would be the best approach for designing this data structure.

Should I have 3 tables for each type and have a view that is designed to allow for fast access to the data, or should I have a single table and use one of the three tree design methodologies described in this article?

What would be the upside and downside of each approach?

Upvotes: 0

Views: 153

Answers (1)

PerformanceDBA
PerformanceDBA

Reputation: 33708

Noting the Relational database tag

The Relational Model handles hierarchies beautifully. There is no need to do strange things, such as in the blog post: it is anti-Relational. The hierarchy is implemented plainly, and all queries are simple. Use SQL, the data sub-language for the RM, to obtain whatever "picture" or "view" you need of the data.

  • If you want Relational, definitely three tables: one table would be horrendous because it is not Normalised.

  • The Primary Keys I am giving you (Client, Entity, Channel) are short names or codes that the user has for Identifying their data. In the second and third tables, they are composites. Such Keys are very fast.

    • ie. it is not a surrogate, such as a Record ID, ID, id. A surrogate is always an additional column and an additional index, which would make the tables distinctly slower. Not to mention, the code required will be buggy and confusing.

  • This is adequate for the level of your question, the SQL code required to navigate the structure is simple.

  • To cater for the "inheritance or override" of the attributes, I can provide a more Normalised design, which will have no NULL columns, however it will require slightly more complex SQL code, which can be eased with the use of a View. Please ask.

Relational Data Model

XerxesTA

Note • Notation

  • All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

  • My IDEF1X Introduction is essential reading for beginners.

Enjoy.

Upvotes: 1

Related Questions