Hi,
I get tripped up at the very beginning of design because I often hear contradictory methodologies in the same paragraph. Or rather I hear a methodology, but the presenters and authors continually forget to address the most basic of questions, which is degree: How far is too far? How much design is too much?
What I hear is don't make flat files (that is, a file where a single table contains too much information that should instead be split up). But what authors forget to address is that flat is relative. So, their examples flatten a lot of information. But why that amount of information? Is it flattened too much, or not enough?
Going hand-in-hand with flattening is do not to duplicate information. But guess what? But guess what - that makes the duplication of information also relative.
For example, many introductory lessons are about making a database for inventory control, or an invoicing system, or some other database where blank has blank. That's the basic of it there: Blank has blank. (eg, these people have some combination of those things, these branch offices have some combination of those employees, these employees do some combination of those duties, these duties require some combination of those educational courses, these educational courses ... and so on).
Welcome to my rabbit hole.
Invariably, I'm told one of the tables should be the people-table (or customers-table, or office-branches-table, or whatever). And the example fields are almost always: Name, Title, Address, City, State, Postal Code, Phone, Birthdate, and/or other basic demographic stuff, and finally some sort of identifying key.
You might see where I'm going with this: Why is this the right number of fields to put in a table? Why not less? If there's many people within the same city, then why not have the city be a separate table? Because what if they move, and you need to know where they moved from and where they moved to? The only truths I know are: Flat is relative, and duplication is relative. Sure, this table as-is may work great matching against a table of items the person has. But what is I want to include more information about the city these people live in? For example, common suppliers that serve the people within the same city? Shouldn't then City and State be in its own table?
Shouldn't sex and gender be separate tables?
It goes on and on.
Help