1

What's the more efficient method of schema design in terms of scalability?

If a database has multiple users, and each user has objects (consisting of data in a longtext, a date, and a unique id), is it more efficient to (1) create a mass table of objects that each have a user column, or (2) create individual tables of objects for each user?

I've seen conflicting answers when researching, database normalization says to make individual columns for each user, while several posts mention that performance is increased by using a mass table.

Edit: changed 'elements' to 'objects' for clarity.

Graham
  • 11
  • 3
  • 1
    What do you mean by 'element'? Do you mean like the physics/chemistry concept? Or do you have some other data element in mind? What types of things about users, and other data, do you need to store? Probably, you're going to want a `userId` column in some of your tables, but we need more information. – Clockwork-Muse Feb 08 '13 at 18:06
  • Element is just an object that holds a longtext, date, and unique id. I edited my post to make that more clear. – Graham Feb 08 '13 at 21:23

2 Answers2

2

In general, you want to create a single table for an entity and not break them into separate tables for users.

This makes the system more maintainable. Queries on the system are consistent across all applications. It also structures the data in the way that databases are optimized for accessing it.

There are a few specialized cases where you would split user data into separate tables or even separate databases. This might be a user requirement ("our data cannot mix with anyone elses"). It may be needed to support various backup and security policies. However, the general approach is to design the tables around entities and not around users.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Having a single table with a column to identify the user is proper relational design. Having to add tables when adding a user sounds very fishy, and may cause problems once you have a large amount of users.

When a single table becomes too large, most database products support so-called partitioning which allows to split a single logical table into multiple physical tables on disk, based on some criteria (e.g. to stay with your example, you could have three physical tables with data for userids 1 - 99999 in partion 1, 100000 - 199999 in partition 2 and 200000 - 299999 in partition 3).

Here's an overview for Oracle.

C. Ramseyer
  • 2,322
  • 2
  • 18
  • 22