current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

My Question, is actually a question about the usability / performance of a concept / idea I had:

The Setup:

Troughout my Database, two (actually three) fields always re-appear constantly: title and description (and created). The title is always a VARCHAR(100) and the description always a TEXT.

Now, to simplify those tables, I thought about something (and changed it in that way): Wouldnt it be more useful to just create a table named content, with id, title, description and created as only fields, and always point to that table from all others?

Example:

table tab has id, key and content_id (instead of title, description and created)

table chapter has id, story_id and content_id (" ")

etc

The Question:

Everything works fine so far, but my only fear is performance. Will I run into a bottleneck, doing it this way, or should I be fine? I have about 23 different tables pointing to content right now, and some of them will hold user-defined content (journals, comments, etc) - so the number of entries in content could get quite high.

Is this setup better, or equal to having title and description in every separate table?

Edit: And if it turns out to be a bad idea, what are alternatives to mantain/copying certain fields like title and description into ~25 tables?

Thanks in advance for the help!

share|improve this question
 
Sounds like a pretty good way of doing it to me. –  OGHaza Nov 18 '13 at 14:54
add comment

1 Answer

up vote 0 down vote accepted

There is no clear answer for your question because it mainly depends on usage of the tables, so just consider following points:

  1. How often will you need write to the tables? In case of many inserts/updates having data in one big table can cause problems because all write operations will target the same table.
  2. How often do you need data stored in table with common data? If title or description are not needed most of the time for your select this can be OK. If you need title every time then take into account that you wile always have to JOIN table with common data.
  3. How do you manage your database schema? It can be easier to write some simple tool for creation/checking table structure. In MySQL you can easily access data dictionary with DESCRIBE table_name or through INFORMATION_SCHEMA database.

I'm working on project with 700+ tables where some of the fields have to be present in every table (when was record created, timestamp of last modification). We have simple script that helps with this, because having all data in one table would be disastrous.

share|improve this answer
 
Thanks, I decided to split the data table up and add those columns to each table separately. Based on your questions - much more initial work, but probably better in the end (less joins, etc) ;) –  user169099 Nov 26 '13 at 7:53
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.