My background is as a database developer, however, I have not worked with design/modeling in a long time. My "gut" tells me when tables are not normalized and I need to ask the designers to go fix the database.
Many moons ago, I worked with Texas Instruments tool (can't remember the name now) to design databases. I seemed to remember that there are some verbal ways of describing entities that are major tip-offs to one-to-one, one-to-many, many-to-many relationships. I was never very good at it, but I remember when you verbally described the two entities, you could easily figure out when you were dealing with one of those relationships. (something like Entity A owns zero, one, or more of Entity B ....Entity B is owned by zero or one Entity A. Knowing that information was supposed to tip one off to how the physical database needed to be built). I remember putting a lot of proposed entities on a white board and grouping them.
I remember determining attributes to entities. I remember one preference was to name entity names in the singular vs. the plural (e.g. Appointment vs. Appointments). I remember "crows feet" and other notations on the relationships (I don't remember whose method we were using to build the ERDs). I remember looking at paper forms trying to identify entities and attributes.
Goal: I guess I am looking for something that would be an easy to understand "best practices" of logical/physical modeling. I am not interested in going into the depth that Codd might have used to describe it at this stage. I remember data modeling is a big field in its self and I can't hope to become an expert in a short time.
My objective, as a volunteer, is to design a small database (under 500 MB) for a charitable organization that is trying to track non-financial data. My secondary objective is to brush up on data modeling / database design for my career goals.
As I build the MS-Access forms, I keep running into cases where I realize I have one-to-many relationships or worse, many-to-many that need to be resolved. As you well know, those are things that should be resolved on paper and never after you are in the middle of populating the database.
Any suggestions on how to focus my search?
