What's better - many small tables or one big t

Question

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 1

I would recommend few tables. Over normalization is difficult to manage and you would end up writing complex queries which ends up with slow performance.

Normalize only when absolutely needed and think in logical terms. With the limited information you provided above, I would go for three tables:

Table 1: PersonalDetails Table 2: Activities Table 3: Miscellaneous

There are other techniques to speed up the performance like clustering etc., which you can use depending upon your need.

Answer 2

Unless every person has the same number of hobbies (IE everyone has 2 hobbies listed), it should be normalized.

Fields that are always 1 to 1 with the person should be in the same table. Age for example. No person will have two different ages.

Answer 3

many small tables i.e. Normalization is best here. it provides flexiblility, reduces redundancy and a better database organization.

Answer 4

There is no correct answer to this question because it largely depends on when and how you are going to be using your data, how frequently it will change, and what the volume of usage will be on the database.

What I would personally do would be to organize your data into logical entities and create tables based on those entities. This is at least where I would start.

Answer 5

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.

Answer 6

There is not database organization that's 100% correct, there's only one that's good enough for your purposes. If you don't foresee surpassing the capabilities of a single good database server in the future, then normalize the data and use plenty of constraints such as foreign keys, cascading deletes and such as that will make your database a joy to work with. On the other hand if you look at the databases of a lot of applications that have billions of requests you'll find that they forgo a lot of these niceties in the name of performance and scalability.