Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is a basic DB design question but I now run multiple times into the same situation which I try to illustrate in the following example.

Lets say I have make a DB-system for messaging in a classifieds platform.

A user publishes many ads and other users can message her for a specific ad.

I thus have an ads table, a user table and a message table in my DB.

Since users message people for specific ads, storing the ad AND the user is redundant.

On the other hand, when listing all incoming messages for a given user it would be much more efficient to have the user name as a field in the message table too, because a user might have many ads and only very few of them have messages.

So should the message table now have a user field or not?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

For proper relational design redundancy should be avoided - anything that can be derived from other data (looking up the user that relates to an advert in this case) should not be stored separately again. This helps enforce the integrity of your data, stopping bugs in your code allowing and with appropriate keys and indexes the performance should not be noticeably impacted.

As we are not in the perfect world assumed by theoretical models with perfect implementations of those models and AI to interpret any query we throw at the database, it is sometimes advantageous to de-normalise your data in the manner you describe for a performance gain - but best practise is to only do this once you have identified a specific performance problem rather than baking it into your design. Never de-normalise your data purely for conveniences sake - instead define your tables in normal form and create views to reduce query complexity in other layers of your code.

Of course if you intend to allow messages about an advert to go to other users, a complaint perhaps which would go to an admin initially and not the user who placed the advert, then you must include the destination user as a specific property as it can no longer be unambiguously derived from other data.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.