0

I have the following table design.

an Event table, with helper tables to hold the event meta data.

each event row has an event type column (regular_event, recurring_route, timed_messages, and more.)

each event type has its own tables to define different things regarding the event - including users. The thing is that it might be referenced directly by a many to many table (users_regular_events) or by a third table or forth table (events -> recurring_routes -> recurring_routes_stations -> recurring_stations -> recurring_stations_users -> users).

would it make sense to create a users_events table that duplicates relations between certain events? because, currently if I want to show each user his events I'll have to join multiple tables. Maybe I need to rethink my entire design?

Improve this question
4
  • 4
    Joins are kind of an integral part of a relational database (a join is just a way to return data that represents the relation part of relational). Why do you think avoiding joins is a goal?
    – Aaron Bertrand
    Commented Jan 8, 2017 at 14:00
  • When you put it that way it makes me feel silly, but I think joins are costly. The more tables you query the more overhead it creates. I think some times a certain db design is built for a quick query of one sort (in my case, being able to do relevant backend actions based on events) and does not fit for other sort of query - showing users their events.
    – gilmishal
    Commented Jan 8, 2017 at 14:05
  • How much overhead does it create to replicate the data? What's the overhead of queries to get the same results from it? How much tuning of your current queries and indexes have you done?
    – Erik Reasonable Rates Darling
    Commented Jan 8, 2017 at 14:57
  • 5
    Why don't you design the system the way it makes logical sense, and deal with problematic joins when they prove to cause performance issues? Don't prematurely optimize just because you think joins are costly. Joins that are performance problems are usually because the queries are horrible (which could be requirements or bad query logic), there are missing indexes, or both.
    – Aaron Bertrand
    Commented Jan 8, 2017 at 15:14

2 Answers 2

Reset to default
4

I agree with Aaron Bertrand that pre-optimization is not a good idea. Relational database management systems are built for joining data. Don't presume a performance problem, observe it, preferably through thorough load testing prior to moving to production, then deal with it if necessary.

As to your question about when does it make sense to duplicate data for querying, the classic scenario is in a data warehouse. Ideally, you want the data to be static, in other words, read only. This can be the case for historical transactional data, for example. In a data warehouse the data is written as close to once as possible and is read many, many times. If this is your scenario, then denormalization for reporting may be a reasonable design choice.

However, any time you introduce redundancy in your data you open yourself to the risk of loss of data quality. This is what the Normal Forms are designed to prevent. Redundancy introduces risk that you need to manage.

As long as you go in with your eyes open, you will be OK.

Improve this answer
5

My suggestion is to design the system the way it makes logical sense, and deal with problematic joins when they prove to cause performance issues. Don't prematurely optimize just because you think joins are costly.

Joins that are performance problems are usually because the queries are horrible (which could be requirements or bad query logic), there are missing indexes, or both.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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