|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185277 is a reply to message #185275] |
Sun, 16 March 2014 20:54   |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 16-3-2014 21:22, 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.
>
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)
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185280 is a reply to message #185275] |
Sun, 16 March 2014 21:29 |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
<noreply(at)example(dot)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".
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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185281 is a reply to message #185275] |
Sun, 16 March 2014 21:34 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
richard <noreply(at)example(dot)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.
|
|
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185284 is a reply to message #185283] |
Sun, 16 March 2014 23:09 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185285 is a reply to message #185277] |
Sun, 16 March 2014 23:20 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Sun, 16 Mar 2014 21:54:18 +0100, Luuk wrote:
> On 16-3-2014 21:22, 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.
>>
>
> 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)
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.
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185287 is a reply to message #185275] |
Sun, 16 March 2014 23:55 |
Geoff Muldoon
Messages: 19
Registered: July 2011
Karma: 0
|
Junior Member |
|
|
noreply(at)example(dot)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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185288 is a reply to message #185285] |
Mon, 17 March 2014 00:05 |
Scott Johnson
Messages: 196
Registered: January 2012
Karma: 0
|
Senior Member |
|
|
On 3/16/14, 4:20 PM, richard wrote:
> On Sun, 16 Mar 2014 21:54:18 +0100, Luuk wrote:
>
>> On 16-3-2014 21:22, 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.
>>>
>>
>> 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)
>
>
> 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,
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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185289 is a reply to message #185280] |
Mon, 17 March 2014 00:12 |
Scott Johnson
Messages: 196
Registered: January 2012
Karma: 0
|
Senior Member |
|
|
On 3/16/14, 2:29 PM, Lew Pitcher wrote:
> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
> <noreply(at)example(dot)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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185290 is a reply to message #185287] |
Mon, 17 March 2014 00:13 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Mon, 17 Mar 2014 10:55:57 +1100, Geoff Muldoon wrote:
> noreply(at)example(dot)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
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.
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185291 is a reply to message #185284] |
Mon, 17 March 2014 01:13 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 3/16/2014 7:09 PM, richard wrote:
> 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.
>
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.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185292 is a reply to message #185289] |
Mon, 17 March 2014 01:14 |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
On Sunday 16 March 2014 20:12, in comp.lang.php, "Scott Johnson"
<noonehome(at)chalupasworld(dot)com> wrote:
> On 3/16/14, 2:29 PM, Lew Pitcher wrote:
>> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
>> <noreply(at)example(dot)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--
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185293 is a reply to message #185288] |
Mon, 17 March 2014 01:43 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Sun, 16 Mar 2014 17:05:44 -0700, Scott Johnson wrote:
> On 3/16/14, 4:20 PM, richard wrote:
>> On Sun, 16 Mar 2014 21:54:18 +0100, Luuk wrote:
>>
>>> On 16-3-2014 21:22, 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.
>>>>
>>>
>>> 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)
>>
>>
>> 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,
>
> 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
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.
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185295 is a reply to message #185292] |
Mon, 17 March 2014 02:47 |
Scott Johnson
Messages: 196
Registered: January 2012
Karma: 0
|
Senior Member |
|
|
On 3/16/14, 6:14 PM, Lew Pitcher wrote:
> On Sunday 16 March 2014 20:12, in comp.lang.php, "Scott Johnson"
> <noonehome(at)chalupasworld(dot)com> wrote:
>
>> On 3/16/14, 2:29 PM, Lew Pitcher wrote:
>>> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
>>> <noreply(at)example(dot)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--
>
All Points valid and taken.
Scotty
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185296 is a reply to message #185292] |
Mon, 17 March 2014 02:54 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 3/16/2014 9:14 PM, Lew Pitcher wrote:
> On Sunday 16 March 2014 20:12, in comp.lang.php, "Scott Johnson"
> <noonehome(at)chalupasworld(dot)com> wrote:
>
>> On 3/16/14, 2:29 PM, Lew Pitcher wrote:
>>> On Sunday 16 March 2014 16:22, in comp.lang.php, "richard"
>>> <noreply(at)example(dot)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--
>
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...
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185297 is a reply to message #185290] |
Mon, 17 March 2014 03:54 |
Geoff Muldoon
Messages: 19
Registered: July 2011
Karma: 0
|
Junior Member |
|
|
noreply(at)example(dot)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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185300 is a reply to message #185293] |
Mon, 17 March 2014 04:17 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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, denismfmcmahon(at)gmail(dot)com
|
|
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185308 is a reply to message #185275] |
Mon, 17 March 2014 13:56 |
Gabriel
Messages: 11
Registered: March 2014
Karma: 0
|
Junior Member |
|
|
On 2014-03-16 20:22:39 +0000, richard said:
> 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.
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
|
|
|
|
| Re: Mr. Stuckle and Mr. Miller - explain normalisation with an example [message #185401 is a reply to message #185292] |
Tue, 25 March 2014 14:28 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]