Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Mr. Stuckle and Mr. Miller - explain normalisation with an example

6 views
Skip to first unread message

richard

unread,
Mar 16, 2014, 4:22:39 PM3/16/14
to
I'll bet you won't.

The table I have now consists of the following columns.
songid,hits, title, author, license.
Please explain how this data should be normalised.
And why.
Thank you.

Luuk

unread,
Mar 16, 2014, 4:54:18 PM3/16/14
to
The column 'author' should be an 'authorId'.

why?
- Because 1 author can write more than 1 song
- An author has properties to, like i.e.:
- birtdate
- date of death (if applicable)
- city of birth
- etc
- etc
Another advantage of this is that, when you found out that you
misspelled the name of this author, you only need to change 1 record to
correct the songs of this author.

Another example of this might be 'license'
If i ask questions like:
When became this license valid?
And 'until when was this a valid license'?
Did the author have more than 1 license?

You might say "this is not applicable to my application"
But, in real life, one should be prepared that this will become
applicable for your application. (Because users only start to think
after they see the portential of your application)


Luuk

unread,
Mar 16, 2014, 4:56:53 PM3/16/14
to
On 16-3-2014 21:22, richard wrote:
> I'll bet you won't.

And Richard, please.......

Be a smart man, and do not respond to the stupid reactions of others.
But do not give them the reason to do so

Lew Pitcher

unread,
Mar 16, 2014, 5:29:10 PM3/16/14
to
On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
<nor...@example.com> wrote:

[snipe snipped]
For what it's worth, I don't think that you have to normalize your tables.
Your table design seems to work for you. Normalization won't "fix"
anything, because nothing is "broken".

What normalization (and, a proper database design, for that matter) /will/
do is make data maintenance and manipulation easier and possibly more
efficient. For big datasets, normalization reduces storage costs, improves
data reliability, and makes data manipulation (and the program development
that goes with it) more consistant.

You seem to need none of these benefits. So, normalization is not for you.
Perhaps, when your application and database grow larger, you will see the
need for the improvements that database normalization and proper database
design bring.

--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

Doug Miller

unread,
Mar 16, 2014, 5:34:57 PM3/16/14
to
richard <nor...@example.com> wrote in news:1i4cmixzjivy7.fgb9linsyq7u$.dlg@
40tude.net:

> I'll bet you won't.

There are numerous explanations of normalization all over the net. Not my job to teach you,
especially not what you've already demonstrated you're unwilling and unable to learn.

> The table I have now consists of the following columns.
> songid,hits, title, author, license.
> Please explain how this data should be normalised.
> And why.

The chief advantage of normalization is making it much easier to ensure database
consistency during updates. If every piece of information is stored in one place, and one
place only, there is no possibility of two copies of it getting out of synch.

In the specific case of your database, there probably is no benefit to be gained from
normalization -- because once you've loaded the data, it's unlikely to ever change.

> Thank you.

You're welcome.

Jerry Stuckle

unread,
Mar 16, 2014, 6:02:07 PM3/16/14
to
In addition, this is a PHP newsgroup. Database normalization is not
function of PHP.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

richard

unread,
Mar 16, 2014, 7:09:54 PM3/16/14
to
On Sun, 16 Mar 2014 18:02:07 -0400, Jerry Stuckle wrote:

> On 3/16/2014 4:22 PM, richard wrote:
>> I'll bet you won't.
>>
>> The table I have now consists of the following columns.
>> songid,hits, title, author, license.
>> Please explain how this data should be normalised.
>> And why.
>> Thank you.
>>
>
> In addition, this is a PHP newsgroup. Database normalization is not
> function of PHP.

Oh? yet you're the one who keeps demanding normalisation is the only way to
do a databse in this very group.
Not just with me, with others as well.
You're attitude is, if the table has more tnan 3 columns, you need another
table.

richard

unread,
Mar 16, 2014, 7:20:29 PM3/16/14
to
Actually, that should have been artist, not author.
for my purposes, personal information on either the artist, or author is
not necessary.
As one particular song could have been performed by any number of artists,
that is why the artist column is included. To distinguish the songs.
As for the author, that is actually optional.
Most people don't even care to know who the author is.
But it's there for helping to identify the song.

And that particular table I will use to generate quarterly reports to the
licensing agents, BMI and ASCAP.

Geoff Muldoon

unread,
Mar 16, 2014, 7:55:57 PM3/16/14
to
nor...@example.com says...
>
> I'll bet you won't.
>
> The table I have now consists of the following columns.
> songid,hits, title, author, license.
> Please explain how this data should be normalised.

Why do you have BOTH the songid and the (presumably song) title, when it
would be prefereable to have a "Songs" table separate to the one
recording the (presumably web-click) "hits"?

Is it feasible for a song (for a particular author/artist) to be the
subject of more than one "license", e.g. released on different
compilation ablums through different companies?

GM

Scott Johnson

unread,
Mar 16, 2014, 8:05:44 PM3/16/14
to
You see Luuk gave you a great but very simplistic example why your
author (artist) should be kept in the different table that only you
would defend against.

How do YOU account for several artists for a song in your schema?

You may not 'need' the extra data for each artist, but wow would it not
be good to be update-able down the road, it shows foresight and a 'Big
Picture' mentality.

> that is why the artist column is included. To distinguish the songs.
> As for the author, that is actually optional.
> Most people don't even care to know who the author is.
> But it's there for helping to identify the song.

A DB is, for as far as I can tell, NOT normalized with the 'people' in
mind but rather your data polling and parsing. Display is second thought.

Scotty


Scott Johnson

unread,
Mar 16, 2014, 8:12:31 PM3/16/14
to
On 3/16/14, 2:29 PM, Lew Pitcher wrote:
> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
> <nor...@example.com> wrote:
>
> [snipe snipped]
>
>> The table I have now consists of the following columns.
>> songid,hits, title, author, license.
>> Please explain how this data should be normalised.
>> And why.
>> Thank you.
>
> For what it's worth, I don't think that you have to normalize your tables.
> Your table design seems to work for you. Normalization won't "fix"
> anything, because nothing is "broken".

Nobody said his DB was broken rather an effort to....(read next section)

>
> What normalization (and, a proper database design, for that matter) /will/
> do is make data maintenance and manipulation easier and possibly more
> efficient. For big datasets, normalization reduces storage costs, improves
> data reliability, and makes data manipulation (and the program development
> that goes with it) more consistant.
>

What for the most part most have been trying to explain. And yes at
time it gets in the weeds out of frustration but the foundation has been
solid all along.

> You seem to need none of these benefits. So, normalization is not for you.
> Perhaps, when your application and database grow larger, you will see the
> need for the improvements that database normalization and proper database
> design bring.

It is when your DB is small that you want to incorporate a normalized
design so you can work out exactly what you need before you start having
to code and run DB routines to normalize it.

Nightmare scenario.

Kind of like saying, don't get health insurance until you are
sick....wait. Never mind

Scotty

richard

unread,
Mar 16, 2014, 8:13:28 PM3/16/14
to
Yes it can be.
There have been a few cases of same titles by different authors.
One that cames to mind is "Little Sister" sung by Elvis and another totally
different as sung by Connie Stevens.
It is the author who signs with a licensing agent, not the artist.

www.45cat.com has a collection of most songs of the sixties.
And other years.Most labels show the licensing agents, but many do not.

Jerry Stuckle

unread,
Mar 16, 2014, 9:13:59 PM3/16/14
to
No. My attitude is, if the database is not properly normalized,
normalize it!

You seem to equate this to "a table having more than 3 columns".
Nothing is further from the truth!

But not only do you not understand that, you are unwilling to learn.

Lew Pitcher

unread,
Mar 16, 2014, 9:14:54 PM3/16/14
to
On Sunday 16 March 2014 20:12, in comp.lang.php, "Scott Johnson"
<noon...@chalupasworld.com> wrote:

> On 3/16/14, 2:29 PM, Lew Pitcher wrote:
>> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
>> <nor...@example.com> wrote:
>>
>> [snipe snipped]
>>
>>> The table I have now consists of the following columns.
>>> songid,hits, title, author, license.
>>> Please explain how this data should be normalised.
>>> And why.
>>> Thank you.
>>
>> For what it's worth, I don't think that you have to normalize your
>> tables. Your table design seems to work for you. Normalization won't
>> "fix" anything, because nothing is "broken".
>
> Nobody said his DB was broken

Perhaps. I haven't been following every post.

The point is that richard's current design will not, of itself, cause a
problem. It will, however (as I implied later) be less than optimal.

But, if richard isn't concerned about optimal performance or design, then
there's nothing that normalization will bring to him.

> rather an effort to....(read next section)

Yes, I'm familiar with the effort. I've "assisted" richard a couple of
times, and find his design and implementation less than... what I would do.

But, I'm not him.

>> What normalization (and, a proper database design, for that matter)
>> /will/ do is make data maintenance and manipulation easier and possibly
>> more efficient. For big datasets, normalization reduces storage costs,
>> improves data reliability, and makes data manipulation (and the program
>> development that goes with it) more consistant.
>>
>
> What for the most part most have been trying to explain. And yes at
> time it gets in the weeds out of frustration but the foundation has been
> solid all along.
>
>> You seem to need none of these benefits. So, normalization is not for
>> you. Perhaps, when your application and database grow larger, you will
>> see the need for the improvements that database normalization and proper
>> database design bring.
>
> It is when your DB is small that you want to incorporate a normalized
> design so you can work out exactly what you need before you start having
> to code and run DB routines to normalize it.
>
> Nightmare scenario.

Very true. And, most of the "you need normalization" and "you need to
redesign your data table structure" comments have been well-meant attempts
to address that scenario.

> Kind of like saying, don't get health insurance until you are
> sick....wait. Never mind

Yup.

Richard has passed on the health insurance. Nothing that we say will change
his mind. All we are doing is cluttering up an unrelated newsgroup with
unrequested advice that won't be listened to.

Bottom line: richard's app works to his satisfaction, without a proper
database design and without database normalization. I think that the
subject is closed, and it is time to move on.

--30--

richard

unread,
Mar 16, 2014, 9:43:35 PM3/16/14
to
I defend my use of this table by the fact that it is merely for
recordkeeping reasons.
The only data that is changed is the hits.
The licensing agents want to see my playlist and how many plays each song
had.
So the information is for when I create the printout and sned it to them.
Ad the fact that I can't remember what the hell song number 64-444 is.

Scott Johnson

unread,
Mar 16, 2014, 10:47:59 PM3/16/14
to
All Points valid and taken.

Scotty

Jerry Stuckle

unread,
Mar 16, 2014, 10:54:33 PM3/16/14
to
The problem here is in a couple of months Richard will want something
else which can't be done with his current design - at least not without
rewriting his entire site again. Then he'll come crying back here again...

Geoff Muldoon

unread,
Mar 16, 2014, 11:54:36 PM3/16/14
to
nor...@example.com says...

> >> The table I have now consists of the following columns.
> >> songid,hits, title, author, license.
> >> Please explain how this data should be normalised.
> >
> > Why do you have BOTH the songid and the (presumably song) title, when it
> > would be prefereable to have a "Songs" table separate to the one
> > recording the (presumably web-click) "hits"?
> >
> > Is it feasible for a song (for a particular author/artist) to be the
> > subject of more than one "license", e.g. released on different
> > compilation ablums through different companies?
> >
> > GM
>
> Yes it can be.
> There have been a few cases of same titles by different authors.
> One that cames to mind is "Little Sister" sung by Elvis and another totally
> different as sung by Connie Stevens.
> It is the author who signs with a licensing agent, not the artist.

And you have both the songid and the title in the table for ... ??

GM

Denis McMahon

unread,
Mar 17, 2014, 12:17:40 AM3/17/14
to
On Sun, 16 Mar 2014 21:43:35 -0400, richard wrote:

> I defend my use of this table by the fact that it is merely for
> recordkeeping reasons.

This is part of the fundamental problem.

You're duplicating data that is held elsewhere in the database in the new
table.

You shouldn't be creating tables based on the functions for which they're
being used, you should be designing tables according to data which is
being held in the whole database, normalised in such a way that the
attributes of an object are all stored in one place associated with that
object, and the links between objects are defined by the relationships.

In this case, you describe a table that contains:

songid, hits, title, author, license

However I'm sure you already have at least one (and possibly ten) table
(s) that link songid / title / author, because I suspect that you have
those columns in each of the 1960, 1961, 1962 .... 1969 tables.

So why are you duplicating that data in yet another table, and how are
you going to ensure data consistency is maintained between the 10 year
tables and the hits table (and by hits I assume you mean the number of
plays that is recorded)?

--
Denis McMahon, denismf...@gmail.com

Derek Turner

unread,
Mar 17, 2014, 5:37:18 AM3/17/14
to
On Sun, 16 Mar 2014 21:14:54 -0400, Lew Pitcher wrote:

> Bottom line: richard's app works to his satisfaction, without a proper
> database design and without database normalization. I think that the
> subject is closed, and it is time to move on.

But where's the fun in that?

Gabe

unread,
Mar 17, 2014, 9:56:38 AM3/17/14
to
richard,

Whilst I know that your title quite clearly only wants to ellicit
responses from Mr. Suckle and Mr. Miller quite a few others have been
chipping in. I'd like to offer my pennies-worth too and I know that you
would not want me to feel left out.

Don't worry about data normalisation. You only need to normalise data
as much as is required to achieve the results you want, considering the
possible future that your site may have. It would be sensible to store
author info in a seperate table and link to it using IDs which being an
integer would provide very fast joins.

Don't forget that modern NoSQL databases that are quickly gaining
momentum in the web development world (for good reason - they are very
fast) such as MongoDB don't really even understand data normalisation
as a concept. You just shove the whole lot in, author and all, and
pull the whole lot out and sod repeating yourself.

IMO I do think that from what I have read of your particular site it is
a good fit for the more traditional relational-database approach.

Kind regards

Gabriel

Thomas 'PointedEars' Lahn

unread,
Mar 25, 2014, 10:28:00 AM3/25/14
to
Lew Pitcher wrote:

> "Scott Johnson" wrote:
>> Kind of like saying, don't get health insurance until you are
>> sick....wait. Never mind
>
> Yup.
>
> Richard has passed on the health insurance. Nothing that we say will
> change his mind. All we are doing is cluttering up an unrelated newsgroup

You are confusing cause and effect. It was “richard” who started this off-
topic discussion. The appropriate reaction is either to ignore the thread,
or to crosspost to a database newsgroup and set Followup-To (F'up2) to it.
It is _not_ to continue posting off-topic here and feeding the troll.

> with unrequested advice that won't be listened to.

Probably not by them. But this is not a newsgroup just for “richard”.
In fact, it might be better for everyone if this was not a newsgroup for
“richard” and people with a similar attitude.

F'up2 poster

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.
0 new messages