What's better - many small tables or one big table?
Solution 1:
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 called
a surrogate key. The sole purpose of a surrogate key is to
guarantees a 1 to 1 relationship between it and a real person in the real world. Use the
surrogate key when associating the value of some other attribute to a "person" in
your 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 person
has 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 a
common table with PersonId
as the key. The number of attributes in each table should not
be of concern at this point.
Multi valued attributes such as Hobby need a slightly different
treatment. You might want to create separate tables for each multi-valued attribute. Using Hobbies as an
example you might create the following table PersonHobby(PersonId, Hobby)
. A row in this table might look
something like: (123, "Stamp Collecting")
. This way you can record as many
hobbies as required for each person, one per row. Do the same for "Interest", "Skill" etc.
If there are quite a number of multi-valued attributes
where the combination of PersonId + Hobby
determine nothing else (ie. you don't have anything interesting
to record about this person doing this "Hobby" or "Interest" or "Skill") you could lump them into
an Attribute-Value table having a structure something like PersonAV(PersonId, AttributeName, Value)
. Here a row might
look like: (123, "Hobby", "Stamp Collecting")
.
If you go this route, it is also a good idea to substitute
the 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 is
commonly done so that if you ever need to know which AttributeNames
are valid in your database/application
you have a place to look them up. Think about how you might validate whether "Interest" is a valid value for
AttributeName
or not - if you haven’t recorded some person having that AttributeName
then there is
no 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't
see any of these dependencies in your example so consider the following: Suppose we have a warehouse
full of parts, the PartId
determines its WeightClass
, StockCount
and ShipCost
. This suggests a table
something like: Part(PartId, WeightClass, StockCount, ShipCost)
. However if relationship exists between
non-key attributes then they should be factored out. For example suppose WeightClass
directly
determines 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 dependencies that exist between all of the attributes in your data model in order to do it properly. Just coming up with the functional dependencies takes a fair bit of thought and consideration - but it is critical to getting to the proper database design.
I encourage you to take the time to study normalization a bit more before building your database. A few days spent here will 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".
Solution 2:
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.
Solution 3:
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.