Horizontal Database and Vertical Database

Question

I am working on a social networking site with a family tree that is GEDCOM-compliant. We need to decide if we should use a horizontal or vertical database structure for User profiles. So, I would like to know if anybody can answer when to use a horizontal database structure and when to use vertical database structure.

I found some answers for shopping sites where fields are not decided: a vertical database structure should be used. But I am confused about what to use for a family tree site. Should I use vertical or horizontal?

Answer 1

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

Gedcom is a standard for information exchange so you know how many columns you will have. Maybe the standard is extended with new properties in the future but it probably wont be a lot of new properties. You can easily extend a table with a few new columns.

I would use a 'horizontal' table and not a entity-attribyte-value-system (vertical table). Vertical table systems tend to be slow. They can't be properly indexed and confuse the query optimizer.

It becomes a different story when your users can define new properties in their profiles like eye colo(u)r or favourite colo(u)r themselves. How flexible do you want those profiles to be?

Answer 2

Vertical databases are great for detawarehousing and read/only reporting. Normally you re-generate them overnight. Their write performance is usually very bad however SELECTs are 10-100 times faster.

A typical scenario for using a vertical database is olap reporting when you create a (daily) snapshot of data and then run queries against it. Most of benefit comes from the queries that request only a relatively small number of fields, e.g. when you select only a handful of fields from a wide and large table. Such a query query against millions of records (e.g. calculating SUM/COUNT/AVG) will take only a second or two.

Your case doesn't seem to be a good candidate for a vertical database.

Answer 3

I agree with tuinstoel, vertical table/EAV system is not only slow but also some time very complex. Sometimes it is required to write some of your own api methods that deal with those tables and developers only deal with those methods to avoid complexity.

So if you don't need to add more fields then stay with horizontal table. However you may be need to different table if you also if you are going to support multilingual capability. But I advise to still stick with horizontal tables.

I am also developing an site involving user profile and I am using horizontal tables and if there will be different languages support required in future then I will modify only for fields where language will matter.