I'm trying to decide the best way to structure my database tables to keep them normalized.
I'm saving some data regarding fitness activities. The current activities are:
At first I was thinking of keeping them all activities in one table (ActivityDetails), with the list of actual activities themselves in a separate table (Activities).
Now I'm questioning whether I should keep all activities in one table or if each activity should have it's own table. The problem is that while every activity has some common fields like ActivityId, MemberId, Date, Distance and a few others, each activity will also have some fields that are unique to that activity.
The advantage of keeping all of the activities in one table (ActivityDetails), is that I will sometimes need to play back all of the recent activities from one member or from all members, regardless of the type of activity. If they are all in one table it wouldn't require extra joins to do this.
On the other hand it seems like a bad idea to have columns that will often be empty because they are not related to all activity types.
What is the proper way to handle this?
it depends mainly on how many columns they have in common, versus how many columns are unique to a specific activity
for more info, do a search for "subtype/supertype" -- this has been discussed several times in this forum