CODEWITHSUNDEEP
databasedatabase-design
Roger Jarvis
Roger Jarvis

Reputation: 434

Database schemas - splitting table instead of having a relationship

Say I have a table with 5000 records, and another table containing a list of 5 topics. Each topic is associated with 1000 records in the larger table - each comment has a 'topic' field which is a foreign key to the topics table.

For example, if the database stores all user's comments on a website. There will be 1000 comments on topic A, 1000 on topic B etc...

If I want to obtain all the comments on a particular topic, I would have to write a query to obtain the correct 1000 rows out of a possible 5000. What if instead I created 5 tables, each table storing comments about a particular topic only.

Assuming there would never be more than maybe 40 topics, is this a sensible approach to database design? I can't see any obvious disadvantages, but it seems like it will produce faster query results.

Upvotes: 0

Views: 360

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29619

Frank Schmitt is right.

I'm assuming you don't have much experience with relational databases - it's worth reading up on them (Joe Celko's got a couple of books that might help). The problem you describe is actually one of the key problems RDBMSes were designed to solve; they do this with indexes, foreign keys, and SQL. It's a good idea to learn about this if you're working with an RDBMS, because there's a standard way of solving these problems, and most developers are familiar with them.

There are occasions when these tools aren't enough, or when real-life performance issues force you to design solutions that are not "standard" - they tend not to occur with 5000 records, though. You should only consider those solutions if you can prove you have a problem, because they might solve one constraint, but usually at the expense of other problems.

So, if you can prove your 5000 record database is too slow, and you've optimized everything else, thrown more hardware at it, cached it, and run out of options, then you might consider splitting the tables in the way you describe. It creates a maintenance head ache, and your database access code becomes hard to read - and new developers who pick up the project will have a WTF moment, and need training and documentation.

Upvotes: 2

Frank Schmitt
Frank Schmitt

Reputation: 30765

Don't go down that road. It will not be faster, but it will soon become a maintenance nightmare because

  • you'll have to add a new table for every new topic
  • you'll have to do a lot of UNION ALL ... style queries if you want comments for all topics, and you'll have to modify every one of them if the list of topics changes (although this can be mitigated by clever usage of views)
  • you'll have to drop a table every time you want to get rid of a topic

Just put all comments in a single table, add a foreign key with an index, and you'll be fine (5000 records is a very small amount of data, BTW - RDBMS systems usually handle millions of rows without any problems).

Upvotes: 2

Related Questions