[thelist] SQL help - I think JOINing is required

rudy r937 at interlog.com
Mon Nov 12 07:42:46 CST 2001


> One of the most alarming things in the course, for me, was
> the subject of database normalisation.

alarming?  that's a good one  ;o)


> It only covered the first three rules (don't ask me to cite them... (: ),
> all of which made sense to me, and addressed design problems
> which I'm confident I would have dealt with without knowing the letter
> of the rules themselves.

indeed

a lot of database design is like that -- eminently sensible, even if some
people like to obfuscate it with weird rules that remind you of math class

you should see fourth through sixth normal form  ;o)

mind you, it's hard to state the rules in simple terms -- "don't put stuff
into this table that, um, really belong in that table..."


>That said: in Marc's case these tables are all, at the moment, 1 to
>1. There may be another table sometime in the future that introduces
>a 1 to many relationship off general.ID. Would you work on a 'it's 1
>to 1 at the moment, so stick them in a single table' premise (I'm
>thinking of the problem of data redundancy here, in particular)?

i would consolidate those tables regardless of future relationships with
other tables

the redundancy that is removed by consolidating 1-1 tables is basically
just the foreign keys

but as i suggested yesterday, you gain simplicity (especially in the query)
even though you sacrifice infinitesimally on performance -- for example, if
you only need general info columns for a particular query, the database
still has to retrieve full rows off the disk in order to extract the
general info columns for you, so it's a bit less efficient, but probably
only  by microseconds, so it doesn't matter unless there are millions of
records...


> ... or, use the current four table solution so future use of the
> General.ID for 1 to many selects is more efficient?

no, future 1-many relationships will always point back to the GenInfo
table, you will not see any change to the current structure, whether it is
four tables or one

but yes, if future tables are involved in queries, obviously the simplicity
of one table rather than four comes into play


> As a *very* highly experienced db designer, do you pay a lot
> of attention to the rules of normalisation, or are you more likely
> to analyze a 'real-world' use and scope of the database?

these are not mutually exclusive -- in fact they overlap almost completely


> How much emphasis would you put on separating out data into
> different tables when it's not strictly-speaking dependant on the key?

a lot

the most important part of database design is understanding the keys

i'm not talking about indexes, but rather logical keys

you will often hear me use the terms primary key and foreign key -- these
are the most important database design concepts

all else is trivial if your database keys are done right


> In short: is database normalisation a religious process:
> a faith, or one that you're more than happy to override as
> necessary?

for beginners, always normalise to 3rd normal form

after a while, you will learn when it's okay to break the rules   ;o)


rudy






More information about the thelist mailing list