Database Design basics 4 n00b

[OTOTO]

I have been attempting database design. Please help.

Rethinking the layout, I may be getting stumped by my excel-expectations. I have combined the two tables in excel into 1 table with 33 columns. I am thinking of each row as a very unique record and I am not intuiting the breakdown of entities and attributes.



Access Wizardry wants me to create tables for all repetitious data. I see value in this for reaching 3NF but I don't see why it is necessary. May there be situations when 2NF is enough?

Following the 3NF Normalization I will have at least 2 tables that contain only 2 columns, some data and a primary key. Is my proclivity to limit the number of tables in the database without merit?

[JoeM]

Is my proclivity to limit the number of tables in the database without merit?

Yes. Database design should not be driven by wanting to limit the number of tables. That will actually often lead you down the wrong path. It should be driven by the Rules of Normalization.
Your goal should be to design your tables up to 3NF. Even if you don't immediately see the benefit of it right now, it could really save you a ton of work in the long run.

I speak from experience. Like you, I came to Access from Excel. My first database in Access was a big one, and I did not design my tables according to the Rules of Normalization. Three months into the project, I was having a heck of a time creating queries which, in theory, were being straightforward and should have been fairly easy. Instead, they were a huge pain, involving a lot of subqueries. Then an Access guru pointed out my database design flaws. The bad news was I had to re-design all my tables. The good news was that things became much easier to program after I did that.

You are at least ahead of where I was back then, as you are already aware of the Rules of Normalization and know what you should do. So save yourself the aggravation and program according to those. You won't regret it.

[orange]

I'm with Joe on this!

Also 33 columns in a database table is somewhat rare. Not unheard of, but often Normalized Tables do not approach 33 columns.

Go for the Normalized table design; review your relationships; get some test data and test your data model against your business rules. When you're comfortable that all is OK, then create your database, forms, reports etc.

Good luck.

Update: I just read your other post and the discussion with ItsMe. Excel experience may be a negative to understand database.

The walk before you run and learning Normalization may best be learned by working through this tutorial. You have to work through it, but you will learn the basics - quickly.

[ButtonMoon]

I have been attempting database design. Please help.

Rethinking the layout, I may be getting stumped by my excel-expectations. I have combined the two tables in excel into 1 table with 33 columns. I am thinking of each row as a very unique record and I am not intuiting the breakdown of entities and attributes.

Access Wizardry wants me to create tables for all repetitious data. I see value in this for reaching 3NF but I don't see why it is necessary. May there be situations when 2NF is enough?

Following the 3NF Normalization I will have at least 2 tables that contain only 2 columns, some data and a primary key. Is my proclivity to limit the number of tables in the database without merit?

To be clear, 3NF does NOT mean that you need to create new tables for "repetitious" data. Just because a value repeats on multiple rows that doesn't mean it needs its own table. In fact, proper application of Third Normal Form never requires you to move just a single attribute out of a table and replace it with a foreign key reference to another table. What you have described does sound as if you may be making a design more complex than it needs to be.

Let's take a very simple example: a table of employees with a column for Sex. Let's say that Sex has two possible values, i.e. Male and Female. Obviously those two values will repeat a lot of times if you have many employees! Does that mean that 3NF requires a new table to be created just for Sex? Definitely not. You would need to create new table for the Sex attribute if and only if there happened to be a non-key determinant of sex (sex determined by something other than a key of the employee table - probably unlikely!).

Hope this helps.

[ItsMe]

...Let's take a very simple example: a table of employees with a column for Sex...

Why not have a table for gender? It makes perfect sense to me. Now when you model your DB it is simpler. You do not have to describe Value Lists that reside in forms, etc.

My preference is to place it in a table. Even if you decide 1 is a constant for "Male", 2 is a constant for "Female", and 3 is a constant for "Not Answered"; you will have a table that can be easily referenced.

Sometimes normalization can be taken too far and it can create issues when trying to create queries. But, creating tables for things that might be handled with controls like Option Groups and Combos with value lists does not complicate things. Even if you decide to use a control like an Option Group, having a table to reference the Option Group's values is helpful.

[ButtonMoon]

Why not have a table for gender? It makes perfect sense to me. Now when you model your DB it is simpler. You do not have to describe Value Lists that reside in forms, etc.

My point was that 3NF does not require you to create such a table. You might want to do it for other reasons but 3NF doesn't require you to create new tables just because some value repeats on multiple rows.

[ItsMe]

My point was that 3NF does not require you to create such a table...

Considering the example of gender, I believe this to be true.

[JoeM]

There is an interesting discussion of just that topic here (separate table for Gender). Ben McCormack did a nice job of summarizing when you would want to split Gender out to a separate table, and when it would be unnecessary. A lot of that depends on business requirements (what you will actually be doing with the data).
http://stackoverflow.com/questions/3...-going-too-far