1

I just got a new project (in JAVA) where I am thinking of all the pros and cons of a particular DB design approach. Some pointers:

  1. Our app has many "content" entities like News, Blogs, CMS pages, Wikis, Suggestions etc. I feel that most of these entities are similar (having 75% same fields) but 25% are different. For example, blogs might have a few extra columns which News does not have.

  2. Since every entity seems "similar", I am thinking of keeping them in the same DB table with all the columns covering each entity.

  3. Data load: A customer might have 100K news, 50K blogs, 10K CMS pages, 100K wikis and 50K Suggestions. This is the top-end (maximum) a customer might have given our product. Some customers might have just 2K-5K items of each type in their DB. Lower end data load: 1K items of each type in DB.

  4. There will be more "reads" than "inserts" in this app (customer usage).

My questions:

  1. Will having all this data in a single table hurt performance too much? Note that we will be using Lucene for searching.

  2. Having a single content table with an entity type column which specifies the entity type, we can have a single business layer handling these entities. This will reduce the code base considerably. Any cons of this approach?

1
  • 1
    Option #3... a single table that holds data for all columns that exist in every entity type, together with a type indicator; and a secondary table for each individual entity type that holds unique columns for that specific type (linked to the master table with the same PID for ease of JOINs) – Mark Baker Dec 23 '14 at 12:21
1

Note, that I assume C# (since this language is present in tags) and Entity Framework.

Both approaches assumes, that entity types are inherited from abstract, non-persistent base entity type, which accumulates 75% of fields, so the code base discussion is not relevant.

Pros of Table-Per-Hierarchy (all descendants are stored in single table):

  • database structure is simpler (just one table)
  • queries are simpler (with Table-Per-Type you need joins to select descendant's row)
  • auditing is simpler

Cons of Table-Per-Hierarchy:

  • the table has low-density data (a number of nullable fields, filled dependent on row type). This isn't a problem, when there are 5-6 such fields, but what if there are 20-25 of them?
  • table relations and indexes structure are not obvious. Since your entities will have relations with other entities, this will lead you to foreign key madness. Just imagine foreign keys for you table.
  • database structure isn't obvious. Who can guess, that news are stored beside CMS pages?
  • usually, 90% of entity types have an Id and Name-like fields (Title, Header, etc). Are you going to store all of them, using single table?
  • table size will grow very fast. With the lapse of time some operations (e.g., deletion) will be slower.

IMHO, every inheritance scheme, mapped to a relational database, should be used very carefully. Definitely you shouldn't map all you entities to a single table.

1

When i did a systems analysis module in my years at university, i was doing my computer science degree. One thing our lecture told us all at the time was something we couldn't comprehend.

"Strictly speaking, there is no wrong or right way to approach DBs"

As computer scientists this is hard to swallow.

lets look at your example:

Ok, so you have entities that are similar. They only differ on lets say, a "type". So your idea is to use one table, then use this "type" field to differentiate. Any extra columns that a blog may have will simply be null in the others.

Pros? Easy to manage, easy to query, removes duplicating fields, you could say this is the correct way.

Cons? Field growth is large (messy), Table size is vast, lots of null-able cells, multiple access has a strong potentials to cause problems updating the table. So, in a way, your way is also wrong.

So taking the basic evaluation there is no wrong way, there is no right way in the rational sense of thinking most programmers love. This is unfortunately its right if it suits your needs.

You need to think about impact. Ok i gain super simple query's, this helps me if im doing some reporting, great stuff. What if this website has large volumes of traffic? you going hit a world of problems having one "super" table of data.

The best methodology I approach DBs is to go against the current fashion of what code 1st approach trys to do. First stop thinking you can throw programming concepts at it. duplicate data is fine depending on reasoning. (please note, this actually rarely happens if you stick to good principles and use normalization correctly, however you can still do this if it suits your needs)

Start normalizing your entity's, you already know what they are, start breaking them down, all the while keeping a record of the layout (usually a diagram of sorts is best). once you have these base entitys start the process of normalizing those entity's further, just be careful not to over normalize.

While from a code perspective this seems alot of work, you have some complex query's to produce. This is the clash, data should be attainable, it should be used, look at expected traffic to drive your DB design, general concept is high traffic = more normalization, low traffic = less.

Finally, to your questions, will it hurt performance? Yes, however the cost of table indexing vs relational joins (assuming relations db design) is a whole issue on its own that needs looking into. while you gain simple querys and less code, the performance difference isnt that different that anyone would expect you will be suprised, however the real issue is locks, even with appropiate indexs, there will be immense locks taking place, when this starts to happen you will see huge performance losses, and annoying logs!

yes your code will reduce but the pressure on you db is increased, if performance isnt and issue however then the is nothing stopping you doing it that way.

My opinion? again let me stress this is all opinion based, there really is no wrong or right way, there are alot of factors involved for your model. However i would split your entitys up, create your diagram to show the layout.

hope this hasn't confused you, DBs are a funny thing where 1 and 0, true false doesnt really apply to approach. generally though i lik to think of 2 things, performance vs maintainability.

0

My opinion:

  1. Data duplication would be make complex and costly to updates and inserts.

  2. It is always application-specific and needs to be re-evaluated if the application changes.

  3. It can increase the size of tables.

0
  1. not really as long as it's all indexed properly.

  2. The issue you (or some other poor so and so) might run into is updates and maintainability. For example six months after it's gone live a new field needs to be added to, say, the news page. You've got the same amount of work to do as if it were separate tables but now you've got to do a load more testing to make sure that field isn't being used on other pages.

You're better off with separate tables where data is not repeated. Keep it simple, keep it logically separated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy