Using Vertical and Horizontal Table Structures in Oracle, Page 2

Vertical Table Grouping

The variations of vertical data storage can include custom algorithms for aggregation of logical data rows. What this means is that more then on id column can be used to group properties. For example:

ID	Grouping	KEY	VALUE
123	1	name	Vlad
123	1	phone	555-555-5555
123	2	machine	Dell 560
123	2	ISP	Verizon
234	1	name	Mariya
234	1	phone	555-456-8392
234	1	email	m@mail.com
243	2	machine	Dell 1750

When a custom report is generated, it can be split easily into pages or sections based on the second "grouping" column. Therefore, a Vertical Table above can become this report:

Report 1
	Page 1
		Name	Phone
		Vlad	555-555-5555
	Page 2
		Machine: Dell 560
		ISP: Verizon

 

Report 2
	Page 1
		Name	Phone	Email
		Mariya	555-456-8392	m@mail.com
	Page 2
		Machine: Dell 1750

Note how different reports and pages have a diverse number of entries in them.

Conclusion

In this article, I have shown you different ways to persist data in a database table. The benefits and flexibility offered by a vertical data layout are often negated by its shortcomings. But, its flexibility is often the only way to design data organization. On the other hand, horizontal tables are better suited for data normalization and are a traditional way to persist data in RBMS. It will be up to software and database architects to decide what type of database table design is the right choice for a specific application need.

About the Author

Vlad Kofman works on enterprise-scale projects for the major Wall Street firms. He has also worked on defense contracts for the U.S. government. His main interests are object-oriented programming methodologies, UI, and design patterns.