Menu
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?
346
Horizontal database scaling involves adding more servers to work on a single workload. Most horizontally scalable systems come with functionality compromises. If an application requires more functionality, migration to a vertically scaled system may be preferable.
How is this different from horizontal scaling? While horizontal scaling refers to adding additional nodes, vertical scaling describes adding more power to your current machines. For instance, if your server requires more processing power, vertical scaling would mean upgrading the CPUs.
With vertical scaling (“scaling up”), you're adding more compute power to your existing instances/nodes. In horizontal scaling (“scaling out”), you get the additional capacity in a system by adding more instances to your environment, sharing the processing and memory workload across multiple devices.
Horizontal scaling involves adding additional servers and partitioning the system dataset and load over those servers. Vertical scaling involves expanding the resources used by a single server/replica set.
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?
172
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.
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
