MySQL: multiple tables or one table with many columns? [closed]

by

Any time information is one-to-one (each user has one name and password), then it’s probably better to have it one table, since it reduces the number of joins the database will need to do to retrieve results. I think some databases have a limit on the number of columns per table, but I wouldn’t worry about it in normal cases, and you can always split it later if you need to.

If the data is one-to-many (each user has thousands of rows of usage info), then it should be split into separate tables to reduce duplicate data (duplicate data wastes storage space, cache space, and makes the database harder to maintain).

You might find the Wikipedia article on database normalization interesting, since it discusses the reasons for this in depth:

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Denormalization is also something to be aware of, because there are cases where repeating data is better (since it reduces the amount of work the database needs to do when reading data). I’d highly recommend making your data as normalized as possible to start out, and only denormalize if you’re aware of performance problems in specific queries.

More Related Contents:

  1. Cakephp Career Database Design
  2. How to store multiple options in a single table?
  3. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  4. What does character set and collation mean exactly?
  5. How to pivot a MySQL entity-attribute-value schema
  6. Implementing Comments and Likes in database
  7. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  8. MySQL – Conditional Foreign Key Constraints
  9. Auto Generate Database Diagram MySQL [closed]
  10. MySQL stored procedures use them or not to use them
  11. Unique constraint that allows empty values in MySQL
  12. Entity-Attribute-Value Table Design
  13. Storing SHA1 hash values in MySQL
  14. MySQL ENUM type vs join tables
  15. Remove Primary Key in MySQL
  16. Relational database design multiple user types
  17. Modeling Product Variants
  18. Facebook like notifications tracking (DB Design)
  19. How to design a multi tenant mysql database
  20. DOs and DONTs for Indexes [closed]
  21. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  22. MySQL – Supertype/Subtype design
  23. Difference between two table structure
  24. Proper database model for a user feedback system (an interesting case)
  25. When to use a left outer join?
  26. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  27. List of Constraints from MySQL Database
  28. Should I always prefer MySQL InnoDB over MyISAM?
  29. MySQL Relationships
  30. Inventory management with stock options

Leave a Comment