Subject Re: Management Tool specific
Author Adam
--- In, "bmckenna6" <bmckenna@...> wrote:
> Adam,
> Thanks for particpating in this 'dialog' with myself <g>.
> I forgot to mention that all add'l tables would have the
> same exact structure and use a common generator.
> Still a bit new to relational db concepts, I now realize,
> in part by my 'explaining' back to you what I forgot
> to include in my original post, that I can add an add'l
> integer field, tie that to a small and simple TableCaptions
> table, and use the (new to me) concept of Views, which
> will be much more efficient and now only require the
> one table.
> <g> Is this what they mean by normalizing the data?

Normalising is a process by which you remove redundant information
from the database. It is one of those things that is easier to
understand by example than by description.

Someone with a background in spreadsheets moving into databases is
likely to just dump all information in the one table. At a simple
level, you might have a contact database for a company, with a single
Person table.

It might look like:


Now what happens if 'ABC Corp' changes to 'DEF Corp'? You are going to
need to find all instances of those people working for ABC Corp and
change the Company.

A Better Approach would be:



Now a change to the company name is as simple as changing 1 record.

Notice how the Person table no longer stores information about the
Company that has no relevance to the person entity? Consider what
happens to a contact who holds a position at more than one company,
you may want to abstract to a PersonCompany table, and it goes on.

This is obviously drastically simplified and incomplete, what I wanted
to point out was that there are sometimes needs to modify the database
structure. As you are new to database concepts, you may find elements
in your initial design that you become unhappy with and want to
change, or your business rules may move and you need to adapt your
data structures to accommodate the new rules.

The last thing you want is to have Firebird tell you that you can't
drop the field 'CompanyID' because it has 3 dependencies that you
never knew about.

Views are quite useful, they are just queries pretending to be tables.
Fred is not about to follow an ER diagram to work out which phone
number to dial to get Wilma, looking up the, then looking at
personcompany, then looking at company, but you can create a view that
makes it appear to be in a single table, how this helps in your
particular case, I am not sure.