Is this (over?)-normalization

[steanders]

I've been getting a lot of help from this site from just searching the forums, but it's my first post in here, and here we go:

I have a few questions regarding the basic design of a database I'm working on at the moment. A few of the tables looks something like this at the moment (I will only list the relevant-ish columns):

... not sure if we'll use the column prefixes, but that doesn't matter atm.

(table) location
location_name
location_contact_first_name
location_contact_email
location_address_street
location_address_zip
location_phone
city_id
...

(table) organizer
organizer_name
organizer_contact_first_name
organizer_contact_email
organizer_address_street
organizer_address_zip
organizer_phone
city_id

...

I suppose we'll have the same columns for users/members on the site as well. This doesn't go very well along with the basic (?!) normalization principles, right?

But I was talking to a guy at work with a lot of (My)SQL experience (he's more of a database-administrator than a designer/developer though) and his opinion was that it could be "over-normalization" (for our project, we have no intention of really keeping track of/organizing zip-codes etc., and we would definitely not (pre)-populate a zip_code table) to put the email addresses, and the physical addresses, and zip-codes in separate tables.

Before I talked to him, I was just about to create two, or three tables: email_address, address and possibly zip_codes. I suppose each row in the organizer, location and member/user tables would get an email_id, the address would get a zip_code_id and the zip_codes would get a city_id.

Which option makes most sense to you, for our project?

Cheers/skål

[laserlight]

I would ask: what are the entities that I am dealing with? Email addresses, addresses and zip codes do not sound like the entities that you are working with, but rather attributes of say, a member.

[steanders]

Thanks for the reply.

In our case the entities are definitely not zip codes etc. Rather, as you say, attributes of a member. And, by saying this I suppose we have an answer to the question(s).

Using the "design" where, let's say, a zip code is stored in a column will, of course, lead to duplicated/redundant (?!) data. But I suppose that's ok, sometimes. Perhaps this is a very bad comparison, but I wouldn't think of putting, let's say, a first name (eg. "Joe") in a "first_name" table.

[laserlight]

In our case the entities are definitely not zip codes etc. Rather, as you say, attributes of a member. And, by saying this I suppose we have an answer to the question(s).

Yes. Your entities would become tables (unless their relationship with other entities is such that they should be columns), and your attributes would become columns.

Using the "design" where, let's say, a zip code is stored in a column will, of course, lead to duplicated/redundant (?!) data. But I suppose that's ok, sometimes. Perhaps this is a very bad comparison, but I wouldn't think of putting, let's say, a first name (eg. "Joe") in a "first_name" table.

I would think that it is an apt comparison. It is possible that you might want a table where first_name is a key, but more likely you would either have a combination of first_name and surname as the key, or use an auto-incremented primary key column.

Likewise, it may be the case that you want to identify say, users, by their zip codes, but more likely you do not. Since nothing depends on them, it does not matter if there is duplication, since such duplication can be deemed as incidental.

[steanders]

Thanks for your input on this, laserlight. Back to the drawing board again.

[rulian]

yes there is such a thing as over normalization to the point it becomes impractical, as you said, having zip codes it's its own table would seem unnecessary if you are simply collecting the user data and they wont be sharing zips a.

that woudl not be the case if your site needed to specify the zip code to a city, city to county, county to state and so fourth, then each would be reliant on a specific key and therefore need normatlization

it seems to me your tables take the same information for different elements, so you can combine this table by simply adding a field for "type" of entity being entered.

entry_type
(last_)? name
contact_first_name
contact_email
address_street
address_zip
phone
city_id

so sample data woudl be
"organizer"
"robanowsky"
"somedude"
"[e-mail]"
"123 4th street"
"56789"
"867-5309"
"2"

[steanders]

it seems to me your tables take the same information for different elements, so you can combine this table by simply adding a field for "type" of entity being entered.

entry_type
(last_)? name
contact_first_name
contact_email
address_street
address_zip
phone
city_id

so sample data woudl be
"organizer"
"robanowsky"
"somedude"
"[e-mail]"
"123 4th street"
"56789"
"867-5309"
"2"

Seems like a good solution too. Perhaps I would go for (yet another) table for the entry_types and use an entry_id, or perhaps an enum data type? I'm not a big fan of enum though.

Anyway, for our project the member/user table will probably grow to maybe (well, hopefully) to 10.000's or 100.000's of thousands of rows, whereas the tables organizer and location will probably just hold hundreds of rows. I guess it makes sense to not mix these hundreds of rows with 100.000's of rows.

Thanks for your input, as well.

[rulian]

mixing them should not be a problem as long as you are doing the same thing to the data. If you forsee in the future modifying the user's table and not the organizers's table, then you should keep them seperately. So it's all in how you treat the data, if you treat them the same, keep them the same

[steanders]

I was more thinking in terms of having to use a table with, possibly, 100.000's of rows /members when you want to get a location address/phone number etc.

As it looks right now, we will probably use the location addresses very frequently and barely use the member addresses at all.