Are multiple tables REALLY necessary?

Davidc316

From what I've read, table joining is the done thing if you're building an online catalogue.

But, to me it just seems like an unnecessary thing to do.

I mean... let's take the example that's in my PHP book... the goal is to build an online pet store. The author recommends that you use three separate MySQL tables; one for pet type (eg- it's a fish), another table for pet details (eg, it's a goldfish) and a third table for pet color (eg- "it's a white goldfish").

To me, that just seems not only pointless but potentially troublesome. I've noticed already that when you try to delete a record from the database, SOME details can still be lurking around on other tables- it's very easy to forget to go through all of the three tables. So, I think the table joining method is slightly more difficult to maintain.

Anyway, the punchline is... I fail to see why table joining is necessary in the first place. Why not do everything on one table and simply use your website to call upon certain columns depending on what information to want to call up?

laserlight

There's an article in phpbuilder on database normalisation.

I suggest that you read it.

laserlight

ah, here's the article:

http://www.phpbuilder.com/columns/barry20000731.php3

tomhath

The example you give is obviously simplified to illustrate the concept, it is kind of silly to use three tables for that application.

Here's a little more real world: You have a "person" table, an "address" table, and a "phone" table. One person could have several addresses, each address can have several phones (perhaps dozens), or no phone at all. The person can have several phones not associated with an address (cell, pager, etc). This is easy to implement with three tables, what does your one big table look like?

Davidc316

Thanks for the replies folks!

I read that article and it was pretty good, but I'm not going to lie and pretend I understood every single word the guy said.

The reply from Tomhath was very helpful also and I a certainly take your point about how that would be very difficult on a single table.

I'm a newbie. The concept of table joining is entirely new to me and ultimately I think I just need to take a little time to read and think about what's been said.

Thanks again!

(I'll come back and mark this thread as resolved once I've taken on board what's been said. In the meantime, any other comments or recommended articles are welcome.)

DeltaRho2K

I just want to expand on what tomhath was saying to put it into perspective a bit (because I am writing an app where normalization is imperative)

The numbers aren't exact, but I am simplifying it for my ease of showing you:

I am writing an app for a bowling association that has 10 member bowling centers. Each bowling center has 2 leagues per day (14 per week). Each league has 20 teams, and each team has 5 bowlers. Each bowler bowls 3 games each (during their league)

This comes out to: 14,000 bowlers. (42,000 games)

The reason I broke it down is so you can start to visualize my normalization methods.

Without normalization, I would have to add Bowling Center Name, Address, city, state, zip, Phone, Fax, League Name, Leaue's Bowling Night, League's Sanction Status, League's President's Last Name & First name, address, city state, zip, phone, Team Number, Team Name, Bowler's Last Name, Bowler's First Name, Bowler's address, cisty, state, zip, phone, Bowler's ABC ID Number, Bowler's game 1 score, Game 2 score, game 3 score, handicap, average, date the games were bowled. (14,000 times per week)

Nevermind the problems and slowness trying to retrieve one week's worth of scores for one single team, league, or center.

Uh, not going to happen...

Now, with normalization, all I have to add is the Bowler's Uniqe ID Number, game1 score, game 2 score, and game 3 score.

I then use foreign keys and a relational table to figure out which team, league, and bowling center this bowler ID is in.

Also, if another bowling center joins the association, I would have to keep track of yet another bowling center... With normalization, I just add the center to my bowlingcenter table, and add the foreign key to the relational DB. Anytime, I add a new league, I just associate it's foreign key in the relational DB to the center it is in.

I know this post is a bit of overkill, but I really haven't begun to scratch the surface of the importance of normalization on databases... I highly recommend you give strong consideration to it. (You kind of have to plan for the future, not for the now.) You may only be dealing with 100 records now, but how many will you have in 2 years, 5 years, 10?

Also, you have to remember that once you write the SQL, you don't have to think about the actual "maintaining of the data". My signature says it all... Good planning makes for good apps.

Good luck with whatever you decide.

~DR

jamesxi

Normalization? I read the whole article but I don't understand why he uses that bizarre term. Is there some kind of SQL jargon "normalization"? I was under the impression that "normalization"is almost the same as "standardization". I am a newbie as well (I am developing my first real SQL database now).

I think the central idea of the article is pretty simple: Multiple tables should be used to eliminate repeating data. They should also be used to eliminate unused fields.

I don't think I would use "normalized" tables on my own database. It would make simple queries and all sorts of very simple adjustments complicated. I do admit though that it would make detailed queries more simple. By detailed I mean queries that for example find things in common with users. An example based on the article would be a query to see if there are certain types of companies that users worked for. A query on this website might find that people worked for software companies for example. I would still have just one table and would not break it up, as the author suggests.

Also, if the database tables are edited constantly (which I highly doubt would be the case very often) then i can see why it would be a good idea to separate everything into many tables.

I would never break up a table just to save a little bit of hard drive space. Hard drive space is just a couple bucks a gigabyte! I will design my tables for the fastest query times.

PS - I should add that I have 22 tables right now with my shopping cart system. So, obviously I am at least somewhat normal. The author would probably have more like 70.

laserlight

I suspect the term normalisation, when used in this context, comes from Edgar Codd when he defined the first 3 normal forms.

tomhath

Keep reading 🙂

http://home.earthlink.net/~billkent/Doc/simple5.htm

From http://www.dba-oracle.com/oracle_tips_codd_obit.htm

Codd's most famous quote (in my opinion) was when he was asked why he chose the word "normalization" to describe relational database modeling.

Codd was reported to have replied "At the time, Nixon was normalizing relations with China. I figured that if he could normalize relations, then so could I".

Ted Codd may best be remembered for a famous mnemonic on his definition for second-normal-form:

"A relation is in second normal form if the relation depends on the key, the whole key, and nothing but the key, so help me Codd".

laserlight

yes, Codd help us indeed 😃

DeltaRho2K

Don't get me wrong, it isn't for everyone... I mean, if you aren't dealing with a large amount of records, then you really aren't gaining very much by normalising your tables anyhow.

I do have one table in an app I wrote that has redundant data that is all in one single table, but I don't have to concern myself with indexing, or more than about 100 records at any given time. Also, it is a personal app, so I don't have to worry about how it will work for others...

If you don't think you need to normalise your tables, than don't. Just remember to think ahead. If you think you may have a need for it later, then it is much easier to normalise now than later.

Davidc316

Well, these have certainly been very high quality replies so far.

Thanks very much folks!

I haven't checked out the article yet on normalisation, but I'll be sure to have a look.

So far, as a new guy trying to learn php, I have to tell you there have been two concepts that have really stumped me so far. One is this normalisationg stuff (which we're dealing with now) and the other hurdle that's really confusing me is the idea of how sessions work. I've yet to find a book or article that clearly explains how they work and how to use them.

Anyway, that's off topic and I'm waffling, but I just thought I'd mention it. Thanks again!

😃

DeltaRho2K

I chuckle David... You obviously haven't begun to tackle multi-dimensional arrays yet 😉

That's one of those you have to give yourself a Saturday afternoon for. LOL