How to design a multi-year statistics database?

Question

I'm designing a database for name statistics (how many people where given that name). The data consists of names, and numbers for how many men and women were given that name within a time period (primarily 15 year time periods, but this varies). The data is rather simple, but I still keep getting stuck with the schema.

So here are the two (very similar) options I am considering:

1) Just one large table. (Name, CountMen, CountWomen, Timeperiod) Time period would probably be split to start and end columns for easier querying. As per primary key, I could either have autoincremented ID or just use combination of name and start of the time period.

2) I'll have names in a separate table (where they'll be primary keys) and the other table will contain the actual statistics (and thus look like the table in number 1). I've read that having a single-column table is not particularly bad design but I don't know if it makes any sense either or rather adds any value.

The options I have ruled out are:

1) Having a column for each time period because then I would eventually have to update the schema. This just seems like terrible design.

2) Having separate tables for each time period. Because time periods aren't that short, I wouldn't end up with that many

So how would all recommend I approach this? Is there an approach I have not considered? I know it's a simple thing and I should probably just stop overthinking and pick one approach. Still, I'd like a second opinion first because I'm quite new to database stuff.

Answer 1

As Andrew Piliser pointed out normalization will be required based on your design but also based on your data set. From there, denormalization, with lookup tables, may be required based on implementation, performance and data integrity requirement.

Apart from the tables design, you want to create views based on what you want to display to the user. This way you can change your database designs without affecting your applications (rewriting your code and sql statement).

Most databases use views in addition to access frequency and other parameter to minimize response time and reduce disk access.

In academia, we talk about going all the way down to 5th Normal Form. Usefull in data intensive application where every tables can be seen as a distinct object that can not be decomposed any more. As an example an Address is in a City that is in a State/Province that is in a Country , all distinct table.

Answer 2

There's a concept called Third Normal Form that is useful in this kind of situation. The Wiki article is not a great explanation, but the gist of it is that you never want to be in a situation where you could always guess the value in one column from the value in another (unless it's a key). In your case, if you had two columns StartDate and EndDate, it sounds like you will always have the same EndDate if you have the same StartDate. If the time periods could be different for different names, then this doesn't apply.

If you extract the time span into a separate table like (StartDate, EndDate) and have your main table (Name, CountMen, CountWomen, StartDate), then you will be conforming to 3NF. The advantage of this is that you are structurally encoding the requirement that two time periods will never have the same StartDate and a different EndDate. You could also add a constraint (depending on your DBMS) that makes sure time periods don't overlap, if that is something that you want to prevent.

Your instincts are good, and you should avoid situations where you will have to regularly add columns or tables for similar data. Whenever I start thinking about programatically generating columns or tables, it tells me that I need a new table to represent that data. In this case, you thought about adding a column or table for each time period, so that clues me in that a TimePeriod table is something to consider.