I have started to design a database for a company which I work for. I am curious how the professionals would design such a database and what advice you might carry.
Information about the database:
The database is a reflection of activities which are all completely different. Basically there are around seven activities which each carry their own set of information. This means that it is hard to standardize the database and simply make a table called 'activities' which suffices for the entire database.
I was thinking of using a seperate table for each activity and link those to a couple of tables with basic information like the geographic information and/or the employee who was/is involved.
If this is the case then I at least know that I am on the right path and if I am not right, then I have learned something important!
If the information provided is not complete, then feel free to ask for more!
Best Answer
Splitting up the tables into different activities sounds like a much better plan than trying to shoe-horn 7 different activities into 1 table.
Different objects belong in different tables - rule of thumb - better to have many smaller tables that are "thin" - i.e. fewer fields than 1 megatable which is "fat" (i.e. many fields, many of which will have to be
NULL). These sorts of tables are unwieldy and difficult to programme against.Check this out to see what happens if this advice is ignored to the point of lunacy :-)
Finally, if this is your first database project, try a prototype with a couple of activities and see how that goes. Better to make your mistakes on a small project than a big one (and you will make mistakes).
Be sure to have a working, relatively complete, prototype before moving on to the full project.
Come back here if you get stuck on more specific issues - your question is a bit "broad" for this forum. After successfully completing a prototype you should then be ready to move onto a bigger undertaking.