Re: Is my database normalized?

#1

Randall (personal):

Quote:

After trying to normalize the
database, it looks like I might have to do up to 6 inserts after a
user signs up. Is that normal? Is the database normalized well?

Here's the image of the database:
http://img818.imageshack.us/img818/9899/nikeairjdb.png

Yup, looks pretty good.
I just don't see why you have attributes full_name and abbr_name in
State and in Country. Are these fields different from each other?

(Just info: it's not 'singup', but rater 'signup' in 'Activation'

--
Erick

#2

On Oct 4, 1:06*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

Randall (personal):

After trying to normalize the
database, it looks like I might have to do up to 6 inserts after a
user signs up. Is that normal? Is the database normalized well?

Here's the image of the database:
http://img818.imageshack.us/img818/9899/nikeairjdb.png

Yup, looks pretty good.
I just don't see why you have attributes full_name and abbr_name in
State and in Country. Are these fields different from each other?

(Just info: it's not 'singup', but rater 'signup' in 'Activation'

--
Erick

Thanks for catching my spelling error!

Yeah, in the state table, it will be something like this:
(26, "Arizona", "AZ")

and as for the country table, it will be like this:
(1, "United States of America", "USA")

I'm not sure if I'll use the abbreviations yet, but I added it in case
if I want to future proof the database.

#3

Randall (personal):

Quote:

Thanks for your feedback! I feel better about my database structure
now. So, I am guessing that doing about 6 inserts per customer
registration is normal for this type of structure?

In a situation which you describe, yes. Perhaps you should see it this
way: you only insert a customer with one query. The other inserts are
just 'links': you attach that new customer to whatever records of
'standard data' you already have (like camera type or protocol). That's
pretty normal, just like companies that add a new employee, then
link/attach them to a 'coach', a 'department', and several 'projects'.

Be aware that you must still solve a general problem: how do you avoid
duplicates?
For instance: your database already includes the camera "ABC Flash
1.0". As long as your customers can only select cameras from a
generated list from your database, everything is OK. However, if they
can add new cameras, then you may end up with someone who claims to
have "ACB Flash", and others with "ABC Falsh 1.0" or "ABC Flash 1_0""
(where they all actually have the same model, only differently spelled).

--
Erick

#4

On Wed, 6 Oct 2010 09:15:41 -0700 (PDT), RB wrote:

Quote:

Jerry, What exact do you disagree with in your response. For example I
would happily stick something like Home_Phone / Work_phone /
Mobile_Phone into the customer table instead of a phone/phone_type
table sometimes, even though clearly that isn't well normalised like
in the example provided here. I might even have to add Work_phone_2 or
something at a later date but if you know that is extremely unlikely I
can't see any real issues in the denormalised version, it depends on
the business need as pointed out.

There's an appropriate aphorism: Rules exist so that we can afford to
break them.

Which essentially comes down to in this case that it's important to
always have the eye on reaching a reasonable ideal (3NF is usually a
pretty good target). This tends to maximize the flexibility of the
database you're creating. It means that you can add features and
maintain the thing with minimal additional actual code. Setting aside
normalization should be a *decision*, and one made every time it's done,
for clear and well-understood reasons, because that decision may well
make things harder further down the line.

--
72. If all the heroes are standing together around a strange device and
begin to taunt me, I will pull out a conventional weapon instead of
using my unstoppable superweapon on them.
--Peter Anspach's list of things to do as an Evil Overlord

#5

On Thu, 07 Oct 2010 02:55:05 GMT, "Peter H. Coffin"
<hellsop (AT) ninehells (DOT) com> wrote:

Quote:

On Wed, 6 Oct 2010 09:15:41 -0700 (PDT), RB wrote:
Jerry, What exact do you disagree with in your response. For example I
would happily stick something like Home_Phone / Work_phone /
Mobile_Phone into the customer table instead of a phone/phone_type
table sometimes, even though clearly that isn't well normalised like
in the example provided here. I might even have to add Work_phone_2 or
something at a later date but if you know that is extremely unlikely I
can't see any real issues in the denormalised version, it depends on
the business need as pointed out.

There's an appropriate aphorism: Rules exist so that we can afford to
break them.

Which essentially comes down to in this case that it's important to
always have the eye on reaching a reasonable ideal (3NF is usually a
pretty good target). This tends to maximize the flexibility of the
database you're creating. It means that you can add features and
maintain the thing with minimal additional actual code. Setting aside
normalization should be a *decision*, and one made every time it's done,
for clear and well-understood reasons, because that decision may well
make things harder further down the line.

Very nice summary of a reasonable approach to balancing rigor and
practice. If you break a rule, have a good reason.

#6

Heather Mills wrote:

Quote:

Very nice summary of a reasonable approach to balancing rigor and
practice. If you break a rule, have a good reason.

Rules are for the guidance of wise men, and the obedience of fools.

#7

On 10/7/2010 8:51 AM, The Natural Philosopher wrote:

Quote:

Heather Mills wrote:

Very nice summary of a reasonable approach to balancing rigor and
practice. If you break a rule, have a good reason.

Rules are for the guidance of wise men, and the obedience of fools.

And to be criticized and ignored by idiots.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#8

Chad Hanna wrote:

Quote:

On 07/10/2010 03:55, Peter H. Coffin wrote:
On Wed, 6 Oct 2010 09:15:41 -0700 (PDT), RB wrote:
Jerry, What exact do you disagree with in your response. For example I
would happily stick something like Home_Phone / Work_phone /
Mobile_Phone into the customer table instead of a phone/phone_type
table sometimes, even though clearly that isn't well normalised like
in the example provided here. I might even have to add Work_phone_2 or
something at a later date but if you know that is extremely unlikely I
can't see any real issues in the denormalised version, it depends on
the business need as pointed out.

There's an appropriate aphorism: Rules exist so that we can afford to
break them.

Which essentially comes down to in this case that it's important to
always have the eye on reaching a reasonable ideal (3NF is usually a
pretty good target). This tends to maximize the flexibility of the
database you're creating. It means that you can add features and
maintain the thing with minimal additional actual code. Setting aside
normalization should be a *decision*, and one made every time it's done,
for clear and well-understood reasons, because that decision may well
make things harder further down the line.

My approach is to create a logical design in 3NF and then denormalize to
produce a 'physical' design for performance, adding triggers and other
apparatus to retain referential integrity where necessary.

My approach is to avoid people who *talk* about 3NF, and use those who

know what it actually means..;-)

#9

The hyperspace communicator crackled into life and we heard The Natural
Philosopher say:

Quote:

Heather Mills wrote:

Very nice summary of a reasonable approach to balancing rigor and
practice. If you break a rule, have a good reason.

Rules are for the guidance of wise men, and the obedience of fools.

Since I fall into neither category they are of no interest to me. (Douglas
Bader IIRC)

#10

On Thu, 07 Oct 2010 10:41:34 -0400, Jerry Stuckle
<jstucklex (AT) attglobal (DOT) net> wrote:

Quote:

On 10/7/2010 8:51 AM, The Natural Philosopher wrote:
Heather Mills wrote:

Very nice summary of a reasonable approach to balancing rigor and
practice. If you break a rule, have a good reason.

Rules are for the guidance of wise men, and the obedience of fools.

And to be criticized and ignored by idiots.

Aha. I see you are still going around agreeing with everyone and
generally spraying sunshine. Dipshit.