Normalizing this DB

Anon

Very simple question:

I am very new to db design and got very confused reading several articles about normalization. Any ideas how i would set up a sensible database to do the following?

original DB table

| County | City | Venue | Date | Comment_about_night | Pic1 | Pic2 | Pic3 etc etc++ |

But I get stuck when it comes to sorting out the tables / link tables

There could be any number of pics, so i'm guessing that I would need to create a new table for each set?

The county, city and venue and date can all be duplicated but never all together. The comment is the only true inedependent variable.

I've done a mass of backend programing for the site i'm creating but this is my only stumbling block. I have read many DB design / normalization tutorials and can't seem to find any way of reducing this one (i can't apply what i'm reading!)

Can anyone give me any clues?

Anon

I'd just add a new column to teh table you have as it's primary key (call it "EventID").

Create a new table with three columns:

PicID - autoincremented primary key
EventID - link to original table
Pic - (whatever you are storing in the original table)

Then getting the list of pictures for an event is a simple join on EventID. You might want separate tables for County, City, and Venue and store links to them in your main table so you don't repeat the names.

Anon

Well, since I am a huge addict of Normalizing, I would like to go into more detail here. Tomhath is right in what was said, but I think explaining the general concept of normalization is in order... the Aaron Zoller Simplified Explanation that is... And since my own website (AaronZoller.com) will not be open till June 15th of this year, I will have to do the explanation here. Once I open my site I will write an article regarding it, but here goes.

Essentially, normalization is the process of eliminating redundant data. So for example if you have a table (like you said you do), that contains Counties, Cities, Venues, etc, you will quickly realize that the name of the Counties are being duplicated, as are the names of the cities and so on. If you do the math, words that repeat will enevitablly take up more space then numberic ID fields.

Typically, when you normalize a database into the infamous 3rd Normal Form (also known as Domain Key Normalization), the number of tables in your database will grow substantially. In the case of your proposed table, to take it to 3rd Normal you would need to create a table for all the CITIES and give every city a CITY_ID (could even be an autonumber). Then you would create a table for venues and every one would receive a VENUE_ID. Also a table for COUNTIES with a COUNTY_ID field. All those tables have essentially a number field (the ID field) corresponding to a name (a text field). Now once you have done that, you never need to use those words again, just the numeric ID that corresponds to them.

Once everything has been broken out into seperate tables, you bring everything back together at the very end with a LEFT JOIN.

Hope that explains a little. I've got to go back to work (DRAB!!!!) but I'll check back and try and write more if time permits. Also, as I mentioned I will be writing an in-depth article on database normalization on my website AaronZoller.com which again is opening June 15th, 2002.

Okay so I plugged my website a couple times, but at least I'm not an ADDY. I contributed to PHPBuilder so it's ok.

Aaron Zoller
www.AaronZoller.com (Opening June 15th, 2002)

Anon

So there is more that can be said... I briefly explained how to split your Counties, Venues, and Cities into normalized tables, but what about those pics.

Well, for you to store the pics, you need two things. A PIC table with PIC_ID's and and the names and addresses of the pics. Probably three fields, unless your pic's names are the same as the address. Okay so once you have a table of just pics with PIC_ID's corresponding to each of the pictures, you then need another table to link the pics to your main table. Also, I should note here that your MAIN table needs an ID that will uniquely identify the given row. I'm not sure exactly what your db is for, but let us propose that you are storing pictures of PEOPLE. In that case, you would have a PEOPLE table with PEOPLE_ID's. You would then create this "other table" that links your pics with your people. Assuming you're not sure how many pics of each person you will be storing, then this is the way to go. If you knew you only had three pics of each person then it may be more feasible to create 3 columns in the PEOPLE table and store that way. I personally like to plan for the day when my database is huge so limiting it is never a goal of mine. Anyway, I'm digressing, let me go on. This "other table" that will link PEOPLE with PICS will only have two columns in it. One column for the PIC_ID and one columns for the PEOPLE_ID. Does that make sense. You may wish to consider this kind of table a relationship table, as it relates one row of data to another. In fact, let's just say this table is called the PEOPLE_PICS_RELATIONSHIPS table.

In this case, you would then be able to store as many pictures about any one person as you wanted, or none if you so chose to. It elliminates the use of NULL values. Conversely, if you had three columns in your PEOPLE table for three pictures but you only had two pictures in some places, then you would end up with NULL's in the empty fields since there would be no picture to store there. Doing it this way means that if you have no pictures for a person, you have no records in the PEOPLE_PICS_RELATIONSHIPS table.

Okay so now that I have thouroughly exhausted my already tired brain, I will quit writing about this. Oh, maybe I should plug the upcoming AaronZoller.com one more time (two if you count my signature.) The site will be open June 15th, 2002 and will support similar discussions as are seen on the PHPBuilder website. I have set out to develop a more enhanced forum with more features so it will be easier to find the specific information one looks for. Anyway, now I am just rambling. Bye.

AaronZoller.com
(Aaron Zoller is AaronZoller.com as of June 15th, 2002)

tomhath

Aaron's suggestion is more general than mine. I assumed a picture would only be associated with one event, so I put the eventid in the pic table. Storing the pictures without associating them to any event and using a separate table to link them is more normalized (I call that an intersection table).

I would avoid putting multiple links to pics in any table, it's tempting to do that but arrays inside of rows usually cause lots of other complexities and make future expansion very difficult.

mattr

Cities are usually attached to counties, right? I'd probably do something like this:
CREATE TABLE county(
abbr CHAR( 3 ), -- or could be id number
name VARCHAR( 100 )
-- etc.
)

CREATE TABLE city(
cityname VARCHAR( 100 ),
)

-- I can think of several cities which live in more than one county
CREATE TABLE city_county(
cityname,
countyabbr
)

CREATE TABLE venue(
cityname
)
Unless you really, really need to associate a county with a venue (I think city and address should be enough) that should suffice.

mattr

p.s. why a LEFT JOIN ? Is that to avoid MySQL-induced no FK support data inconsitencies (sp)?