Database Design Data Structures – Should Database Table Structure Match Its Intended Data Structure?

data structuresdatabasedatabase-design

This question branches out of this question, What are the differences between algorithms using data structures and algorithms using databases?.

The General Question

Should a database table(s) structure match its intended data structure(s) in the logic?

Some Context

The simplest example (and it may be oversimplified) of what I am thinking about is a database table whose columns match the properties of a linked list data structure. So the properties I think of for a linked list are the following:

Linked List

Node identity.
Item/Content in node.
Next node.

In regards to the general question, the database table columns that I think of getting mapped are the following:

Linked List > Database Table

Node Identity > Primary Key Column(s).
Item/Content in node > Column(s) containing data that is to be persisted.
Next node > Column containing a reference to the record that is to follow it in the same table.

An application I can think of that represents the above is to say, keep track of a trail of points you have been to on a map. So I can imagine saving the coordinates of point A then putting in the mapped Next node column, the coordinates of point B. Letting my imagination run a little further, then retrieve this data and load it into a linked list, use its properties to do the traversal, and draw these points on a map again at a later point in time.

Questions

I can see how creating a mapping like this simplifies thinking about the data, but I also feel it restricts you to thinking about the data in only this way. How conforming do you make your database tables to data structure(s), if at all?
Should databases be viewed as realms of raw data that are to be shaped and molded (translated) to data structures selected dependent on the program's purpose?

I restate what I said in the last post. I know answers without a specific context are difficult. Food-for-thought, advice, or discussion points are mainly what I'm looking for and would be most appreciated! I did try to give a more specific context in this post.

Best Answer

As a database person I want to throw up at the idea of someone designing a database table that way.

Database tables should be designed using three main criteria:

Data integrity
Performance of querying the database
Data security

A good database design often needs fields that are not used by the application in any way (but may be needed for data imports or auditing or for some other reason) and in many databases, multiple applications will need to access the data and have differing needs.

What looks easy to do for data entry screens may not work at all for reporting (which is often a totally different application that the developer designing only for his logic doesn't even know about) because reports tend to deal with larger data sets than data entry screens which typically only concern one record at a time. So a database struture that looks logical from the application programmers perspective (Like an EAV table, shudder!) can be the worst possible thing to use when getting data back out of the database later. Especially if there will be millions of records.

Databases are optimized to use normalized tables and should be designed with normalization priciples in mind not using Object-oriented principles. Yes there is mismatch between what is best for the database and what is best for an object-oriented application. Yes you have to deal with that and no it isn't a good idea to deal with it by making the database match the orject-orientation.

Related Solutions

Data Structures – How to Hold Excel Column Names with Their Values?

Assumed you need a generic solution, where the program is not tied to a specific column structure at run time, you can use an ArrayList of rows, where each row is a string array String[]. You will need an additional HashTable<String,int> to store the mapping of the column name to the column index (a HashMap will probably work, either). So if you have

 HashTable<String,int> columnMapping;
 ArrayList<String[]> tableValues;

you can implement getColumnValue by

 String getColumnValue(String columnName, int indexOfValue)
 {
     return tableValues.get(columnMapping.get(columnName))[indexOfValue];
 }

You may consider to add some error handling here, some code which determines the column names and some more code which reads the excel rows into the String arrays with one entry per column. If you need the values in a typed form, consider to replace the String[] rows by Object[] and store the cell content as objects of type String, Integer, Double or Date. You can also implement a speficic class CellContent and store your rows as CellContent[] arrays, if you have specific requirements and a need to implement specific behaviour or methods for a content element.

Note this is a rough scetch of a general purpose solution. One can modify this and create different variants, for more specific requirements.

If you need a less generic variant, because your program expects a specific column structure, best solution is probably to create a specific DTO class MyRowType for holding the specific values of a row, and use a variable like ArrayList<MyRowType> tableValues for storing the table content.