What's better - many small tables or one big table?

mysql database database-design database-schema
Mazatec picture Mazatec · Nov 3, 2010 · Viewed 30.1k times · Source

I've got a database which will store profiles about individuals. These individuals have about 50 possible fields.

Some are common things like, first name, last name, email, phone number.

Others are things like hobbies, skills, interests

Some are height, weight, skin color.

Each of these groups are used by the system at different times. In terms of being able to negotiate through the database I would prefer to have 7 tables each of about 8 fields. What is best practice to do?

EDIT: The data is going to be used in a search engine, for finding profile matches. Does this affect what I am doing?

Answer

Jimithus picture Jimithus · Nov 3, 2010

It is hard to say, and is based on what the application requires. I would say to look into Database Normalization as it will show you how to normalize the database and in that it should shed light on what you would want to separate out into their own tables etc.

Related questions

Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?

I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries? CREATE TABLE foo( created INT NOT NULL …

Read More
Database Design for storing Chat Messages between people

I am trying to build a messaging/chat system. which can store conversation between two people in a chronological order. Also if User A deletes the conversation User B still should have access the conversation until he wishes to delete …

Read More
How to see table partition size in MySQL ( is it even possible? )

I've partitioned my table horizontally and I'd like to see how the rows are currently distributed. Searching the web didn't bring any relevant results. Could anyone tell me if this is possible?

Read More