Horizontal database and vertical database

Question

I am working on a social networking site with a family tree compatible with GEDCOM. We need to decide whether to use the horizontal or vertical database structure for user profiles. So, I would like to know if anyone can answer when to use the horizontal database structure and when to use the vertical database structure.

I found answers to trading floors where fields are not defined: it is necessary to use the structure of a vertical database. But I'm confused about what to use for a family tree site. Should I use vertical or horizontal?

Answer 1

I assume that you are using a relational database like Mysql, Ms sql, Sqlite, Postgresql or Oracle for storage?

Gedcom is an information exchange standard, so you know how many columns you have. Perhaps the standard will be expanded with new features in the future, but there probably won't be many new features. You can easily expand the table with several new columns.

I would use a "horizontal" table, not a system-entity-attribute-value (vertical table). Vertical table systems are generally slow. They cannot be properly indexed and confuse the query optimizer.

This becomes a different story when your users can define new properties in their profiles, such as eye colo (u) r or favorite colo (u) r. How flexible do you want these profiles to be?

Answer 2

Vertical databases are great for reporting and reading / report-only. You usually re-generate them overnight. Their recording performance is usually very poor, but SELECTs are 10-100 times faster.

A typical use case for a vertical database is an olap message when you create a (daily) snapshot of data and then run queries against it. Most of the benefits come from queries that request only a relatively small number of fields, for example. when you select only a few fields from a large and a large table. Such a query for millions of records (for example, calculating SUM / COUNT / AVG) will only take a second or two.

Your case does not seem to be a good candidate for a vertical database.

Answer 3

I agree with tuinstoel, the vertical table / EAV system is not only slow, but also very complex for some time. Sometimes you need to write some of your own api methods that relate to these tables, and developers use only these methods to avoid complexity.

So, if you do not need to add more fields, then stay with a horizontal table. However, you may need to use a different table if you also support multilingual capabilities. But I advise you to still stick to horizontal tables.

I also develop a site with a user profile, and I use horizontal tables, and if in the future I need to support different languages, I will change only for fields in which the language will matter.