MySQL Database Structure Decisions

Question

I'm trying to decide between having 1 enormous table for all the possible data about a user, a lot of which won't apply to every user, and then having a separate table for the data that a user can have multiple instances of (for example, previous jobs) versus having the data about each user distributed between multiple tables.

The first way is more streamlined but I feel like it would use a ton of unnecessary overhead, whereas the second way results is easier to work with but results in a lot of extra database queries.

Multiple instances of some associated thing *always* go into another table. — Robert Harvey, Oct 11 '12 at 21:29
Is all or a very significant part of the data which might not exist for each user of the same data type? — xception, Oct 11 '12 at 21:30
each user can be of 3 different types and each type of user has up to 20 different qualities. also, ive normalized the tables so that there are no redundancies or anomalies. — user988129, Oct 11 '12 at 21:33

Robert Harvey · Answer 1 · 2012-10-11T21:37:22.490

Multiple instances of some associated thing always go into another table. This process is called normalization.

While it may seem more complicated at first glance, it will make your life easier in the long run. Database systems like MySQL make short work of combining the tables back together again (denormalizing), if needed (provided your key fields are properly indexed).

It's much harder to work with denormalized tables like the one you described, for a number of reasons. Let's say a Person has more than one address. How would you put that into the main table? Address1, Address2, Address3? What if the person has four different addresses?

Working with such a table is going to be much harder, because you now have to deal with three columns in the table instead of one, in every query you write.

He said he was going to have separate tables for the attributes with multiple instances. His question is what to do about all the single-instance attributes, where some of them only apply to certain types of users — Barmar, Oct 11 '12 at 22:33

score 1 · Answer 2 · answered Oct 11 '12 at 22:40

If the relevant qualities depend on the type of user, and all users of a particular type have all those qualities, you could have a table for each type.

CREATE TABLE Type1_Qualities ( id int not null auto_increment primary key, user_id int references (User), qual1 ..., qual2 ..., ... )

and similarly for Type2, and Type3. This avoids having all those extraneous fields for every user, but is simpler than doing lots of joins with a generic attributes table like xception's answer.

xception · Answer 3 · 2012-10-11T21:47:19.483

Depending on how your data is stored you might go for multiple relationship data structure, this is just an example:

CREATE TABLE user (
   id int not null auto_increment primary key,
   name varchar[60] not null
);
CREATE TABLE attributes (
   id smallint not null auto_increment primary key,
   name varchar[20] not null,
   order smallint --optional
);
CREATE TABLE userattributes (
   user int not null references user (id),
   attribute smallint not null references attributes(id),
   value varchar[100] not null,
   order tinyint --optional
);
INSERT INTO textattributes(name) VALUES ('alias'), ('address'), ('hobby')

I marked order fields as optional since you might not care about those

SELECT a.name, ua.value
FROM userattributes AS ua
JOIN attributes AS a
    ON ua.attribute = a.id
WHERE user = :user
ORDER BY a.order, ua.order

You can also join user if you'd like but that data would be duplicated for each row, or you can use a separate query for getting data from user. I'd personally use a second query for that.

yes, thats what i was thinking about. my issue was that if im running a search using criteria from all of those tables, wouldnt i need 3 queries for each search versus 1 large one if the tables are combined? — user988129, Oct 11 '12 at 21:42

MySQL Database Structure Decisions

3 Answers3