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

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.

I'm with the Normalize camp.

Here are a few hints to get you started:

Start with a process to assign some arbitrary unique identifier to each"person". Call this the PersonId or something like that. This identifier is calleda surrogate key. The sole purpose of a surrogate key is toguarantees a 1 to 1 relationship between it and a real person in the real world. Use thesurrogate key when associating the value of some other attribute to a "person" inyour database.

As you develop your database layout you may find surrogate keys necessary (or at least useful)for some other attributes as well.

Look at each attribute you want to manage. Ask the following question: Does any given person have only one value for this attribute?

For example, each personhas exactly one "Birth Date". But how may "Hobbies" can they have? Probably zero to many.Single valued attributes (eg. Birth date, height, weight etc.) are candidates to go into acommon table with PersonId as the key. The number of attributes in each table should notbe of concern at this point.

Multi valued attributes such as Hobby need a slightly differenttreatment. You might want to create separate tables for each multi-valued attribute. Using Hobbies as anexample you might create the following table PersonHobby(PersonId, Hobby). A row in this table might looksomething like: (123, "Stamp Collecting"). This way you can record as manyhobbies as required for each person, one per row. Do the same for "Interest", "Skill" etc.

If there are quite a number of multi-valued attributeswhere the combination of PersonId + Hobby determine nothing else (ie. you don't have anything interestingto record about this person doing this "Hobby" or "Interest" or "Skill") you could lump them intoan Attribute-Value table having a structure something like PersonAV(PersonId, AttributeName, Value). Here a row mightlook like: (123, "Hobby", "Stamp Collecting").

If you go this route, it is also a good idea to substitutethe AttributeName in the PersonAV table for a surrogate key and create another table to relate this key to its description.Something like: Attribute(AttributeId, AttributeName). A row in this table would look something like(1, "Hobby") and a corresponding PersonAV row could be (123, 1, "Stamp Collecting"). This iscommonly done so that if you ever need to know which AttributeNames are valid in your database/applicationyou have a place to look them up. Think about how you might validate whether "Interest" is a valid value forAttributeName or not - if you haven’t recorded some person having that AttributeName then there isno record of that AttributeName on your database - how do you know if it should exist or not? Well look it up in the Attribute table!

Some attributes may have multiple relationships and that too will influence how tables are normalized. I didn'tsee any of these dependencies in your example so consider the following: Suppose we have a warehousefull of parts, the PartId determines its WeightClass, StockCount and ShipCost. This suggests a tablesomething like: Part(PartId, WeightClass, StockCount, ShipCost). However if relationship exists betweennon-key attributes then they should be factored out. For example suppose WeightClass directlydetermines ShipCost. This implies that WeightClass alone is enough to determine ShipCost and ShipCost should be factored out of the Part table.

Normalization is a fairly subtle art. You need to identify the functional dependenciesthat exist between all of the attributes in your data model in order to do it properly. Justcoming up with the functional dependencies takes a fair bit of thought and consideration - but itis critical to getting to the proper database design.

I encourage you to take the time tostudy normalization a bit more before building your database. A few days spent herewill more than pay for itself down the road. Try doing some Google/Wikipedia searches for"Functional Dependency", "Normalization" and "Database Design". Read, study, learn, then build it right.

The suggestions I have made with respect to normalizing your database design are only a hint as to the direction you might need to take. Without having a strong grasp of all the data you are trying to manage in your application, any advice given here should be taken with a "grain of salt".

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: PersonalDetailsTable 2: ActivitiesTable 3: Miscellaneous

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