|
|
| Re: Bad database design can cause unnecessary coding [message #179493 is a reply to message #179492] |
Fri, 02 November 2012 08:53   |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
Am 02.11.2012 08:12, schrieb Tony Marston:
> When you design your database for your PHP application are you aware that some of
> your design decisions can have a detrimental effect when it comes to writing the code
> to access that database?
All your design decisions can have a detrimental or beneficial effect. The word "can"
even is too weak, since the database design is the base of your application.
I don't like the phrasing "code to access the database". What you want to access AND
use is your data.
Maybe I am nitpicking, but precision in wording shows precision in concept. That is
what programmers need.
/Str.
|
|
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179495 is a reply to message #179492] |
Fri, 02 November 2012 11:13 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 02 Nov 2012 07:12:05 +0000, Tony Marston wrote:
> Do you agree with my opinions? Can you think of any more questionable
> design decisions which can be added to this list?
I think that, not only are you a spammer, but you're a crap one. My first
reaction on actually reading the url you posted was "wtf, this jerk can't
even write english!"
The first design paradigm I have for you as a website designer is "Don't
try and write websites in languages you aren't fluent in!"
Rgds
Denis McMahon
|
|
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179498 is a reply to message #179496] |
Fri, 02 November 2012 11:48 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/2/2012 7:34 AM, Goran wrote:
> On 2.11.2012 11:48, Jerry Stuckle wrote:
>> You wouldn't know a good design if it was shown to you, spammer.
>
> And why is that? Why are you acting like a mad man?
>
If he really wanted to start a discussion, he would have started it on
usenet, in an appropriate newsgroup (database design is NOT a PHP topic!).
His only purpose in posting here was to spam his site. He must have run
out of suckers who were willing to pay him (again). Just look at his
site and you can see how his overly-inflated ego doesn't let him realize
how stoopid he looks to knowledgeable people - or what a piece of crap
his "rapid development tool" really is.
But then Tony is well-known here for all of the above.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179499 is a reply to message #179496] |
Fri, 02 November 2012 11:56 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 02/11/12 11:34, Goran wrote:
> On 2.11.2012 11:48, Jerry Stuckle wrote:
>> You wouldn't know a good design if it was shown to you, spammer.
>
> And why is that? Why are you acting like a mad man?
>
Its not an act....
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179504 is a reply to message #179493] |
Sat, 03 November 2012 09:14 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>
> Am 02.11.2012 08:12, schrieb Tony Marston:
>> When you design your database for your PHP application are you aware that
>> some of
>> your design decisions can have a detrimental effect when it comes to
>> writing the code
>> to access that database?
>
> All your design decisions can have a detrimental or beneficial effect. The
> word "can"
> even is too weak, since the database design is the base of your
> application.
>
> I don't like the phrasing "code to access the database". What you want to
> access AND
> use is your data.
>
> Maybe I am nitpicking, but precision in wording shows precision in concept.
> That is
> what programmers need.
>
> /Str.
Instead of nitpicking over the words I used why don't you try and show how
intelligent you are by commenting on the 14 "bad" practices that I have
identified. Do you agree with them? Do you disagree? Do you have any more to
add? Or does your limited experience not go as far as designing the
databases that your applications use? Or don't you write database
applications?
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179505 is a reply to message #179494] |
Sat, 03 November 2012 09:17 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"Jerry Stuckle" wrote in message news:k708e6$fjt$1(at)dont-email(dot)me...
>
> On 11/2/2012 3:12 AM, Tony Marston wrote:
>> When you design your database for your PHP application are you aware
>> that some of your design decisions can have a detrimental effect when it
>> comes to writing the code to access that database? I have been designing
>> databases, and building the applications which use them, for several
>> decades, and I have learned over the years that there are some design
>> decisions which help the developer while others do nothing more than
>> hinder. I have produced a document which lists 14 of these “hindrances”
>> which can be read at
>> http://www.iamaspammer.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html
>>
>>
>> Do you agree with my opinions? Can you think of any more questionable
>> design decisions which can be added to this list?
>>
>> Tony Marston
>>
>> http://www.iamaspammer.net
>> http://www.iamaspammer.org
>
> You wouldn't know a good design if it was shown to you, spammer.
>
Hi Jerry,
I thought that you had put me in your kill file long ago, so why are you
still looking at (and responding to) my posts.
I still see that you are incapable of answering a sensible question with an
intelligent answer.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179506 is a reply to message #179498] |
Sat, 03 November 2012 09:28 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"Jerry Stuckle" wrote in message news:k70buo$2bo$1(at)dont-email(dot)me...
>
> On 11/2/2012 7:34 AM, Goran wrote:
>> On 2.11.2012 11:48, Jerry Stuckle wrote:
>>> You wouldn't know a good design if it was shown to you, spammer.
>>
>> And why is that? Why are you acting like a mad man?
>>
>
> If he really wanted to start a discussion, he would have started it on
> usenet, in an appropriate newsgroup (database design is NOT a PHP topic!).
>
> His only purpose in posting here was to spam his site. He must have run
> out of suckers who were willing to pay him (again). Just look at his site
> and you can see how his overly-inflated ego doesn't let him realize how
> stoopid he looks to knowledgeable people - or what a piece of crap his
> "rapid development tool" really is.
>
> But then Tony is well-known here for all of the above.
>
It *IS* a PHP topic for the simple reason that almost every PHP program
accesses a database, and most databases are designed by the programmer
him/herself. What I have attempted to do in my article is identify those
design decisions which are not actually as good as the designer thinks they
are. Bad designs cause the programmer to write extra code which should not
be necessary. If you follow good design practices you can end up writing
less code, or even have that code generated for you so that you don't even
have to write it. Which do you think is the better idea - writing
unnecessary code, or having that code generated for you?
This should be a topic of interest for all programmers in general, and PHP
programmers in particular (after all, I have been designing and developing
business applications using PHP for the last 10 years or so).
Instead of your usual personal attacks why don't you show the depth of your
intelligence and experience by actually responding to the 14 points
contained in my article? Or is that beyond your capabilities?
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179507 is a reply to message #179495] |
Sat, 03 November 2012 09:31 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"Denis McMahon" wrote in message news:k709tb$h2i$1(at)dont-email(dot)me...
>
> On Fri, 02 Nov 2012 07:12:05 +0000, Tony Marston wrote:
>
>> Do you agree with my opinions? Can you think of any more questionable
>> design decisions which can be added to this list?
>
> I think that, not only are you a spammer, but you're a crap one. My first
> reaction on actually reading the url you posted was "wtf, this jerk can't
> even write english!"
>
> The first design paradigm I have for you as a website designer is "Don't
> try and write websites in languages you aren't fluent in!"
>
> Rgds
>
> Denis McMahon
What is wrong with my english? Why do you think I am not fluent in PHP? Or
are you another one of these trolls who is incapable of responding properly
to a serious article?
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179508 is a reply to message #179506] |
Sat, 03 November 2012 09:56 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 03/11/12 10:28, Tony Marston wrote:
> It *IS* a PHP topic for the simple reason that almost every PHP program
> accesses a database, and most databases are designed by the programmer
> him/herself.
I have to agree with Jerry that it's off topic in this news group, it
had been more on topic at alt.php.sql.
Something that people dislike here and most other usergroups is that
promotion of your own site and most of the time it's done in a usergroup
it's quite low quality or even requiring payment to access the low
quality information.
A better way to do is to start a discussion and show your skills in
there without promoting a website of self-interest.
--
//Aho
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179509 is a reply to message #179492] |
Sat, 03 November 2012 10:04 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 02-11-2012 08:12, Tony Marston wrote:
> When you design your database for your PHP application are you aware
> that some of your design decisions can have a detrimental effect when it
> comes to writing the code to access that database? I have been designing
> databases, and building the applications which use them, for several
> decades, and I have learned over the years that there are some design
> decisions which help the developer while others do nothing more than
> hinder. I have produced a document which lists 14 of these “hindrances”
> which can be read at
> http://www.tonymarston.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html
>
>
> Do you agree with my opinions? Can you think of any more questionable
> design decisions which can be added to this list?
>
> Tony Marston
>
> http://www.tonymarston.net
> http://www.radicore.org
Basic Database Rules
2.The primary key can be of almost any data type, but should not exceed
the size limit for the database (which is usually 255 characters). The
primary key can also span more than one column, and need not be the
first column(s) to be defined for the table.
- This is not a 'basic database rule'. It's more a techinal-note on how
the world (might, because i did not verify) look like. The "size limit
for the database" is for sure something techical, which should not be a
'BASIC' rule...
- Some say you should only use 1 column for your primary key.
7.All database, table and column names should use the standard character
set, which is comprised of letters, numbers and underscores. Attempting
to use symbols may seem "cool" at first, but eventually you will feel
the pain.
9.All databases, both past and present, are case INsensitive when it
comes to names. This means that names such as "foo_bar", "FOO_BAR" and
"Foo_Bar" will all resolve to the same column.
- I would not use underscores in my table or column names.
- a 'basic database rule' should be that consistency in naming should be
used. If you use 'CarsSold' in one table, thant you should not have
another field named like 'cars_wrecked'.
The consistency in naming you tables (and columns) is more important
than the case INsentiviity of it.
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179510 is a reply to message #179507] |
Sat, 03 November 2012 13:08 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sat, 03 Nov 2012 09:31:15 +0000, Tony Marston wrote:
> "Denis McMahon" wrote in message news:k709tb$h2i$1(at)dont-email(dot)me...
>>
>> On Fri, 02 Nov 2012 07:12:05 +0000, Tony Marston wrote:
>>
>>> Do you agree with my opinions? Can you think of any more questionable
>>> design decisions which can be added to this list?
>>
>> I think that, not only are you a spammer, but you're a crap one. My
>> first reaction on actually reading the url you posted was "wtf, this
>> jerk can't even write english!"
>>
>> The first design paradigm I have for you as a website designer is "Don't
>> try and write websites in languages you aren't fluent in!"
> What is wrong with my english? Why do you think I am not fluent in PHP?
> Or are you another one of these trolls who is incapable of responding
> properly to a serious article?
I find it hard to take an article seriously when it contains so many
errors. In addition, you appear to be spamming. My grandmother used to
say "if it waddles like a duck and it quacks like a duck, it's a duck."
However, I'll give you the benefit of the doubt, here's one example:
"There are some people out there who are design but never build."
Now, turning to the technical content of your page, your first rule is:
'Every table requires a primary key. Some databases allow a table to be
created without a primary key, but there is no good reason to use this
"feature".'
Pray tell me, in a database table that maps attendees to events in a many
to many relationship, which column, attendee_id or event_id, would be the
primary key?
Or should I add a third, unused, autoincrement column called mapping_id
just so that I can have a primary key, even though that value is never
going to be used.
In addition, you state that 'Wherever possible fields with the same
content should have the same name.'
I have, in my 20+ years of working with sql, found that it is much more
beneficial to name columns for their purpose within the table, rather
than their purpose within some other table.
e.g. person_id in the person table may map to manager_id or managee_id in
the manager table (which maps managers to their staff), attendee_id in
the mapping of events to attendees, and trainee_id in the mapping of
people to courses they have attended (and all three of those tables break
your first rule, because they are all many to many mappings).
My final word is this. Your cardinal rule, rule 1, implies that you have
no understanding of the use, purpose or benefit of mapping many to many
relationships. If that is the case, then your claimed years of experience
are worth squat, zero, zilch, sweet fuck all. If that is not the case,
then your ability to develop worthwhile, meaningful database design
paradigms based on your claimed years of experience is worth squat, zero,
zilch, sweet fuck all.
gds
Denis McMahon
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179511 is a reply to message #179506] |
Sat, 03 November 2012 14:52 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/3/2012 5:28 AM, Tony Marston wrote:
> "Jerry Stuckle" wrote in message news:k70buo$2bo$1(at)dont-email(dot)me...
>>
>> On 11/2/2012 7:34 AM, Goran wrote:
>>> On 2.11.2012 11:48, Jerry Stuckle wrote:
>>>> You wouldn't know a good design if it was shown to you, spammer.
>>>
>>> And why is that? Why are you acting like a mad man?
>>>
>>
>> If he really wanted to start a discussion, he would have started it on
>> usenet, in an appropriate newsgroup (database design is NOT a PHP
>> topic!).
>>
>> His only purpose in posting here was to spam his site. He must have
>> run out of suckers who were willing to pay him (again). Just look at
>> his site and you can see how his overly-inflated ego doesn't let him
>> realize how stoopid he looks to knowledgeable people - or what a piece
>> of crap his "rapid development tool" really is.
>>
>> But then Tony is well-known here for all of the above.
>>
>
> It *IS* a PHP topic for the simple reason that almost every PHP program
> accesses a database, and most databases are designed by the programmer
> him/herself. What I have attempted to do in my article is identify those
> design decisions which are not actually as good as the designer thinks
> they are. Bad designs cause the programmer to write extra code which
> should not be necessary. If you follow good design practices you can end
> up writing less code, or even have that code generated for you so that
> you don't even have to write it. Which do you think is the better idea -
> writing unnecessary code, or having that code generated for you?
>
> This should be a topic of interest for all programmers in general, and
> PHP programmers in particular (after all, I have been designing and
> developing business applications using PHP for the last 10 years or so).
>
> Instead of your usual personal attacks why don't you show the depth of
> your intelligence and experience by actually responding to the 14 points
> contained in my article? Or is that beyond your capabilities?
>
You are so stoopid you don't even know the difference between a database
and a programming language. But then spammers aren't very smart, and
you're even more stoopid than most spammers. Even your rants on your
own site show that - they have provided a group of us with good laughs.
If it's on topic here, show us where the PHP manual discusses "database
design".
But you can't, because it isn't there. So take your bullshit somewhere
else.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179512 is a reply to message #179505] |
Sat, 03 November 2012 14:54 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/3/2012 5:17 AM, Tony Marston wrote:
> "Jerry Stuckle" wrote in message news:k708e6$fjt$1(at)dont-email(dot)me...
>>
>> On 11/2/2012 3:12 AM, Tony Marston wrote:
>>> When you design your database for your PHP application are you aware
>>> that some of your design decisions can have a detrimental effect when it
>>> comes to writing the code to access that database? I have been designing
>>> databases, and building the applications which use them, for several
>>> decades, and I have learned over the years that there are some design
>>> decisions which help the developer while others do nothing more than
>>> hinder. I have produced a document which lists 14 of these “hindrances”
>>> which can be read at
>>> http://www.iamaspammer.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html
>>>
>>>
>>>
>>> Do you agree with my opinions? Can you think of any more questionable
>>> design decisions which can be added to this list?
>>>
>>> Tony Marston
>>>
>>> http://www.iamaspammer.net
>>> http://www.iamaspammer.org
>>
>> You wouldn't know a good design if it was shown to you, spammer.
>>
>
> Hi Jerry,
>
> I thought that you had put me in your kill file long ago, so why are you
> still looking at (and responding to) my posts.
>
> I still see that you are incapable of answering a sensible question with
> an intelligent answer.
>
I can answer sensible questions in an intelligent manner. I can also
recognize spammers when I see them - and I don't support them.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179513 is a reply to message #179510] |
Sat, 03 November 2012 14:55 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/3/2012 9:08 AM, Denis McMahon wrote:
> On Sat, 03 Nov 2012 09:31:15 +0000, Tony Marston wrote:
>
>> "Denis McMahon" wrote in message news:k709tb$h2i$1(at)dont-email(dot)me...
>>>
>>> On Fri, 02 Nov 2012 07:12:05 +0000, Tony Marston wrote:
>>>
>>>> Do you agree with my opinions? Can you think of any more questionable
>>>> design decisions which can be added to this list?
>>>
>>> I think that, not only are you a spammer, but you're a crap one. My
>>> first reaction on actually reading the url you posted was "wtf, this
>>> jerk can't even write english!"
>>>
>>> The first design paradigm I have for you as a website designer is "Don't
>>> try and write websites in languages you aren't fluent in!"
>
>> What is wrong with my english? Why do you think I am not fluent in PHP?
>> Or are you another one of these trolls who is incapable of responding
>> properly to a serious article?
>
> I find it hard to take an article seriously when it contains so many
> errors. In addition, you appear to be spamming. My grandmother used to
> say "if it waddles like a duck and it quacks like a duck, it's a duck."
>
> However, I'll give you the benefit of the doubt, here's one example:
>
> "There are some people out there who are design but never build."
>
> Now, turning to the technical content of your page, your first rule is:
>
> 'Every table requires a primary key. Some databases allow a table to be
> created without a primary key, but there is no good reason to use this
> "feature".'
>
> Pray tell me, in a database table that maps attendees to events in a many
> to many relationship, which column, attendee_id or event_id, would be the
> primary key?
>
> Or should I add a third, unused, autoincrement column called mapping_id
> just so that I can have a primary key, even though that value is never
> going to be used.
>
> In addition, you state that 'Wherever possible fields with the same
> content should have the same name.'
>
> I have, in my 20+ years of working with sql, found that it is much more
> beneficial to name columns for their purpose within the table, rather
> than their purpose within some other table.
>
> e.g. person_id in the person table may map to manager_id or managee_id in
> the manager table (which maps managers to their staff), attendee_id in
> the mapping of events to attendees, and trainee_id in the mapping of
> people to courses they have attended (and all three of those tables break
> your first rule, because they are all many to many mappings).
>
> My final word is this. Your cardinal rule, rule 1, implies that you have
> no understanding of the use, purpose or benefit of mapping many to many
> relationships. If that is the case, then your claimed years of experience
> are worth squat, zero, zilch, sweet fuck all. If that is not the case,
> then your ability to develop worthwhile, meaningful database design
> paradigms based on your claimed years of experience is worth squat, zero,
> zilch, sweet fuck all.
>
> gds
>
> Denis McMahon
>
Don't support the spammer, Denis. It's beneath you.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179515 is a reply to message #179514] |
Sat, 03 November 2012 15:25 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/3/2012 11:06 AM, Luuk wrote:
> On 03-11-2012 15:54, Jerry Stuckle wrote:
>> I can answer sensible questions in an intelligent manner.
>
> If you can, why are you replying in such an unintelligent way than?
Because I don't support spammers - especially stoopid ones. And that's
all Tony is.
If he really wanted to discuss the topic, he would bring his suggestions
up in a related newsgroup. Instead, he just tries to promote his web
site with an off-topic post to this newsgroup.
But then spammers aren't very smart - and Tony is stoopider than most.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179516 is a reply to message #179504] |
Sat, 03 November 2012 17:01 |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
Am 03.11.2012 10:14, schrieb Tony Marston:
> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>
>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>> When you design your database for your PHP application are you aware that some of
>>> your design decisions can have a detrimental effect when it comes to writing the code
>>> to access that database?
>>
>> All your design decisions can have a detrimental or beneficial effect. The word "can"
>> even is too weak, since the database design is the base of your application.
>>
>> I don't like the phrasing "code to access the database". What you want to access AND
>> use is your data.
>>
>> Maybe I am nitpicking, but precision in wording shows precision in concept. That is
>> what programmers need.
>>
>> /Str.
>
> Instead of nitpicking over the words I used why don't you try and show how
Okay, leave out the nitpicking and take it as constructive proposal.
> intelligent you are by commenting on the 14 "bad" practices that I have identified.
> Do you agree with them? Do you disagree? Do you have any more to add? Or does your
Sounds like a lot of work. Advice is okay, work must be payed.
> limited experience not go as far as designing the databases that your applications
> use? Or don't you write database applications?
>
Sorry, I forgot all since I managed DB2 in the early nineties. Forgot all from 15
years as IT trainer. Forgot more than others ever knew :-)
/Str.
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179517 is a reply to message #179504] |
Sat, 03 November 2012 18:00 |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
Am 03.11.2012 10:14, schrieb Tony Marston:
> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>
>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>> When you design your database for your PHP application are you aware that some of
>>> your design decisions can have a detrimental effect when it comes to writing the code
>>> to access that database?
>>
>> All your design decisions can have a detrimental or beneficial effect. The word "can"
>> even is too weak, since the database design is the base of your application.
>>
>> I don't like the phrasing "code to access the database". What you want to access AND
>> use is your data.
>>
>> Maybe I am nitpicking, but precision in wording shows precision in concept. That is
>> what programmers need.
>>
>> /Str.
>
> Instead of nitpicking over the words I used why don't you try and show how
> intelligent you are by commenting on the 14 "bad" practices that I have identified.
> Do you agree with them? Do you disagree? Do you have any more to add? Or does your
another kind of answer:
The general problem is that you throw condensed knowledge at novices. They most often
won't be able to understand and make use of it.
Then your advice often is simply on the coding convention level, not on the design
level. Coding conventions can be different, but they must be consistent. Example
Gotcha #1: Multi-table JOINS:
First you give a complex select example with outer joins that is outright nonsense,
because the joined tables are not used. Then you advice against table aliases.
My standards: name every integer primary key column with "id". Name every foreign key
with the name of the corresponding object. Table aliases on selects must be standard,
and leave out AS because it is only noise.
Example: the product id is "id", the product id in the orders table is product_id.
This all does not answer the question which design decisions have a detrimental
effect on _coding_ .
/Str.
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179518 is a reply to message #179516] |
Sat, 03 November 2012 19:03 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/3/2012 1:01 PM, M. Strobel wrote:
> Am 03.11.2012 10:14, schrieb Tony Marston:
>> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>>
>>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>>> When you design your database for your PHP application are you aware that some of
>>>> your design decisions can have a detrimental effect when it comes to writing the code
>>>> to access that database?
>>>
>>> All your design decisions can have a detrimental or beneficial effect. The word "can"
>>> even is too weak, since the database design is the base of your application.
>>>
>>> I don't like the phrasing "code to access the database". What you want to access AND
>>> use is your data.
>>>
>>> Maybe I am nitpicking, but precision in wording shows precision in concept. That is
>>> what programmers need.
>>>
>>> /Str.
>>
>> Instead of nitpicking over the words I used why don't you try and show how
>
> Okay, leave out the nitpicking and take it as constructive proposal.
>
>> intelligent you are by commenting on the 14 "bad" practices that I have identified.
>> Do you agree with them? Do you disagree? Do you have any more to add? Or does your
>
> Sounds like a lot of work. Advice is okay, work must be payed.
>
>> limited experience not go as far as designing the databases that your applications
>> use? Or don't you write database applications?
>>
> Sorry, I forgot all since I managed DB2 in the early nineties. Forgot all from 15
> years as IT trainer. Forgot more than others ever knew :-)
>
> /Str.
>
Matt, you're arguing with an idiot. He's so stoopid he doesn't even
understand there are things he doesn't know. He thinks he's the world
expert, and anyone who disagrees with him (even widely recognized
experts) are wrong.
He's just finding excuses to spam his crap site - and add more "tips" to
prove how "knowledgeable" he is. The only problem is, to those who are
really knowledgeable, he looks like an ass. And, unfortunately, there
are people who will try his tricks and go way off on the wrong track.
He's even worse than TNP. At least TNP doesn't spam.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179519 is a reply to message #179510] |
Sun, 04 November 2012 10:04 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"Denis McMahon" wrote in message news:k734vv$so2$1(at)dont-email(dot)me...
>
> On Sat, 03 Nov 2012 09:31:15 +0000, Tony Marston wrote:
>
>> "Denis McMahon" wrote in message news:k709tb$h2i$1(at)dont-email(dot)me...
>>>
>>> On Fri, 02 Nov 2012 07:12:05 +0000, Tony Marston wrote:
>>>
>>>> Do you agree with my opinions? Can you think of any more questionable
>>>> design decisions which can be added to this list?
>>>
>>> I think that, not only are you a spammer, but you're a crap one. My
>>> first reaction on actually reading the url you posted was "wtf, this
>>> jerk can't even write english!"
>>>
>>> The first design paradigm I have for you as a website designer is "Don't
>>> try and write websites in languages you aren't fluent in!"
>
>> What is wrong with my english? Why do you think I am not fluent in PHP?
>> Or are you another one of these trolls who is incapable of responding
>> properly to a serious article?
>
> I find it hard to take an article seriously when it contains so many
> errors. In addition, you appear to be spamming. My grandmother used to
> say "if it waddles like a duck and it quacks like a duck, it's a duck."
>
> However, I'll give you the benefit of the doubt, here's one example:
>
> "There are some people out there who are design but never build."
So you found a single little mistake which both I and my spellchecker
missed, and that cause you to disregard the whole article. How petty you
are!
> Now, turning to the technical content of your page, your first rule is:
>
> 'Every table requires a primary key. Some databases allow a table to be
> created without a primary key, but there is no good reason to use this
> "feature".'
>
> Pray tell me, in a database table that maps attendees to events in a many
> to many relationship, which column, attendee_id or event_id, would be the
> primary key?
If you bothered to read the article you would have come across this:
http://www.tonymarston.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html#many-to-many
> Or should I add a third, unused, autoincrement column called mapping_id
> just so that I can have a primary key, even though that value is never
> going to be used.
>
> In addition, you state that 'Wherever possible fields with the same
> content should have the same name.'
>
> I have, in my 20+ years of working with sql, found that it is much more
> beneficial to name columns for their purpose within the table, rather
> than their purpose within some other table.
>
> e.g. person_id in the person table may map to manager_id or managee_id in
> the manager table (which maps managers to their staff), attendee_id in
> the mapping of events to attendees, and trainee_id in the mapping of
> people to courses they have attended (and all three of those tables break
> your first rule, because they are all many to many mappings).
While that may be OK sometimes, the point I was trying to make was that you
should avoid have different names such as ACCOUNT_NO, ACCNO and ACC_NUM
which mean the same thing. You should also avoid using a name which has
different meanings on different tables.
> My final word is this. Your cardinal rule, rule 1, implies that you have
So you disagree with the cardinal rule which says that every table should
have a primary key? That says a lot about your capabilities. I would hate to
work on a database that *YOU* designed.
> no understanding of the use, purpose or benefit of mapping many to many
> relationships. If that is the case, then your claimed years of experience
> are worth squat, zero, zilch, sweet fuck all. If that is not the case,
> then your ability to develop worthwhile, meaningful database design
> paradigms based on your claimed years of experience is worth squat, zero,
> zilch, sweet fuck all.
>
> gds
>
> Denis McMahon
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179520 is a reply to message #179508] |
Sun, 04 November 2012 10:09 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"J.O. Aho" wrote in message news:afk82dF3gokU1(at)mid(dot)individual(dot)net...
>
> On 03/11/12 10:28, Tony Marston wrote:
>
>> It *IS* a PHP topic for the simple reason that almost every PHP program
>> accesses a database, and most databases are designed by the programmer
>> him/herself.
>
> I have to agree with Jerry that it's off topic in this news group, it had
> been more on topic at alt.php.sql.
Are you saying that it is forbidden to post anything related to SQL on this
newsgroup? What about HTML? CSS? JavaScript?
> Something that people dislike here and most other usergroups is that
> promotion of your own site and most of the time it's done in a usergroup
> it's quite low quality or even requiring payment to access the low quality
> information.
I pointed to a separate article for the simple reason that it was too large
to post here. I was not saying "please visit my site which contains lots of
stuff".
> A better way to do is to start a discussion and show your skills in there
> without promoting a website of self-interest.
If you can't be bothered to read the article and respond intelligently to
the points raised in it then don't respond at all.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179521 is a reply to message #179516] |
Sun, 04 November 2012 10:11 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"M. Strobel" wrote in message news:afl0ueF96jhU1(at)mid(dot)uni-berlin(dot)de...
>
> Am 03.11.2012 10:14, schrieb Tony Marston:
>> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>>
>>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>>> When you design your database for your PHP application are you aware
>>>> that some of
>>>> your design decisions can have a detrimental effect when it comes to
>>>> writing the code
>>>> to access that database?
>>>
>>> All your design decisions can have a detrimental or beneficial effect.
>>> The word "can"
>>> even is too weak, since the database design is the base of your
>>> application.
>>>
>>> I don't like the phrasing "code to access the database". What you want
>>> to access AND
>>> use is your data.
>>>
>>> Maybe I am nitpicking, but precision in wording shows precision in
>>> concept. That is
>>> what programmers need.
>>>
>>> /Str.
>>
>> Instead of nitpicking over the words I used why don't you try and show
>> how
>
> Okay, leave out the nitpicking and take it as constructive proposal.
>
>> intelligent you are by commenting on the 14 "bad" practices that I have
>> identified.
>> Do you agree with them? Do you disagree? Do you have any more to add? Or
>> does your
>
> Sounds like a lot of work. Advice is okay, work must be payed.
>
>> limited experience not go as far as designing the databases that your
>> applications
>> use? Or don't you write database applications?
>>
> Sorry, I forgot all since I managed DB2 in the early nineties. Forgot all
> from 15
> years as IT trainer. Forgot more than others ever knew :-)
>
> /Str.
So even with all those years of experience you are still unable to reply
intelligently and constructively to the 14 points I raised in my article?
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179522 is a reply to message #179517] |
Sun, 04 November 2012 10:18 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"M. Strobel" wrote in message news:afl4egF9vukU1(at)mid(dot)uni-berlin(dot)de...
>
> Am 03.11.2012 10:14, schrieb Tony Marston:
>> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>>
>>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>>> When you design your database for your PHP application are you aware
>>>> that some of
>>>> your design decisions can have a detrimental effect when it comes to
>>>> writing the code
>>>> to access that database?
>>>
>>> All your design decisions can have a detrimental or beneficial effect.
>>> The word "can"
>>> even is too weak, since the database design is the base of your
>>> application.
>>>
>>> I don't like the phrasing "code to access the database". What you want
>>> to access AND
>>> use is your data.
>>>
>>> Maybe I am nitpicking, but precision in wording shows precision in
>>> concept. That is
>>> what programmers need.
>>>
>>> /Str.
>>
>> Instead of nitpicking over the words I used why don't you try and show
>> how
>> intelligent you are by commenting on the 14 "bad" practices that I have
>> identified.
>> Do you agree with them? Do you disagree? Do you have any more to add? Or
>> does your
>
> another kind of answer:
>
> The general problem is that you throw condensed knowledge at novices. They
> most often
> won't be able to understand and make use of it.
>
> Then your advice often is simply on the coding convention level, not on the
> design
> level. Coding conventions can be different, but they must be consistent.
> Example
> Gotcha #1: Multi-table JOINS:
>
> First you give a complex select example with outer joins that is outright
> nonsense,
> because the joined tables are not used. Then you advice against table
> aliases.
>
> My standards: name every integer primary key column with "id". Name every
> foreign key
> with the name of the corresponding object. Table aliases on selects must be
> standard,
> and leave out AS because it is only noise.
>
> Example: the product id is "id", the product id in the orders table is
> product_id.
Yuk! That's the complete opposite of what I preach.
> This all does not answer the question which design decisions have a
> detrimental
> effect on _coding_ .
>
> /Str.
Design decisions in the database *DO* affect coding as some decisions
require more coding than others with some SQL queries having to be
individually crafted by hand. I personally prefer decisions which require
less coding as it requires less hand crafting and can sometimes be
automated.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179523 is a reply to message #179509] |
Sun, 04 November 2012 10:30 |
Tony Marston
Messages: 57
Registered: November 2010
Karma: 0
|
Member |
|
|
"Luuk" wrote in message news:vq6fm9-97b(dot)ln1(at)luuk(dot)invalid(dot)lan...
>
> On 02-11-2012 08:12, Tony Marston wrote:
>> When you design your database for your PHP application are you aware
>> that some of your design decisions can have a detrimental effect when it
>> comes to writing the code to access that database? I have been designing
>> databases, and building the applications which use them, for several
>> decades, and I have learned over the years that there are some design
>> decisions which help the developer while others do nothing more than
>> hinder. I have produced a document which lists 14 of these “hindrances”
>> which can be read at
>> http://www.tonymarston.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html
>>
>>
>> Do you agree with my opinions? Can you think of any more questionable
>> design decisions which can be added to this list?
>>
>> Tony Marston
>>
>> http://www.tonymarston.net
>> http://www.radicore.org
>
> Basic Database Rules
>
> 2.The primary key can be of almost any data type, but should not exceed the
> size limit for the database (which is usually 255 characters). The primary
> key can also span more than one column, and need not be the first column(s)
> to be defined for the table.
>
> - This is not a 'basic database rule'. It's more a techinal-note on how the
> world (might, because i did not verify) look like. The "size limit for the
> database" is for sure something techical, which should not be a 'BASIC'
> rule...
It needs to be identified as a rule in order to stop some newbie from using
a large TEXT or BINARY column as a primary key. If you don't state it as a
rule then how is a newbie to know that he's breaking it?
> - Some say you should only use 1 column for your primary key.
>
> 7.All database, table and column names should use the standard character
> set, which is comprised of letters, numbers and underscores. Attempting to
> use symbols may seem "cool" at first, but eventually you will feel the
> pain.
> 9.All databases, both past and present, are case INsensitive when it comes
> to names. This means that names such as "foo_bar", "FOO_BAR" and "Foo_Bar"
> will all resolve to the same column.
>
> - I would not use underscores in my table or column names.
I would, for the simple reason that I started my career using operating
systems which only had a single case, so using mixed case, as in "FooBar"
was out of the question, and underscore was the standard separator, thus
giving "foo_bar". If you choose to be different then that is your choice.
> - a 'basic database rule' should be that consistency in naming should be
> used. If you use 'CarsSold' in one table, thant you should not have another
> field named like 'cars_wrecked'.
> The consistency in naming you tables (and columns) is more important than
> the case INsentiviity of it.
Case sensitive software causes more problems than it solves, which I why I
take steps to avoid potential problems by keeping all my database, table and
column names in lower case with the underscore separator.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179524 is a reply to message #179522] |
Sun, 04 November 2012 12:29 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/4/2012 5:18 AM, Tony Marston wrote:
> "M. Strobel" wrote in message news:afl4egF9vukU1(at)mid(dot)uni-berlin(dot)de...
>>
>> Am 03.11.2012 10:14, schrieb Tony Marston:
>>> "M. Strobel" wrote in message news:afhg10Ff1jjU1(at)mid(dot)uni-berlin(dot)de...
>>>>
>>>> Am 02.11.2012 08:12, schrieb Tony Marston:
>>>> > When you design your database for your PHP application are you
>>>> > aware that some of
>>>> > your design decisions can have a detrimental effect when it comes
>>>> > to writing the code
>>>> > to access that database?
>>>>
>>>> All your design decisions can have a detrimental or beneficial
>>>> effect. The word "can"
>>>> even is too weak, since the database design is the base of your
>>>> application.
>>>>
>>>> I don't like the phrasing "code to access the database". What you
>>>> want to access AND
>>>> use is your data.
>>>>
>>>> Maybe I am nitpicking, but precision in wording shows precision in
>>>> concept. That is
>>>> what programmers need.
>>>>
>>>> /Str.
>>>
>>> Instead of nitpicking over the words I used why don't you try and
>>> show how
>>> intelligent you are by commenting on the 14 "bad" practices that I
>>> have identified.
>>> Do you agree with them? Do you disagree? Do you have any more to add?
>>> Or does your
>>
>> another kind of answer:
>>
>> The general problem is that you throw condensed knowledge at novices.
>> They most often
>> won't be able to understand and make use of it.
>>
>> Then your advice often is simply on the coding convention level, not
>> on the design
>> level. Coding conventions can be different, but they must be
>> consistent. Example
>> Gotcha #1: Multi-table JOINS:
>>
>> First you give a complex select example with outer joins that is
>> outright nonsense,
>> because the joined tables are not used. Then you advice against table
>> aliases.
>>
>> My standards: name every integer primary key column with "id". Name
>> every foreign key
>> with the name of the corresponding object. Table aliases on selects
>> must be standard,
>> and leave out AS because it is only noise.
>>
>> Example: the product id is "id", the product id in the orders table is
>> product_id.
>
> Yuk! That's the complete opposite of what I preach.
>
>> This all does not answer the question which design decisions have a
>> detrimental
>> effect on _coding_ .
>>
>> /Str.
>
> Design decisions in the database *DO* affect coding as some decisions
> require more coding than others with some SQL queries having to be
> individually crafted by hand. I personally prefer decisions which
> require less coding as it requires less hand crafting and can sometimes
> be automated.
>
That's because you don't know how to properly write code.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179525 is a reply to message #179520] |
Sun, 04 November 2012 12:32 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/4/2012 5:09 AM, Tony Marston wrote:
> "J.O. Aho" wrote in message news:afk82dF3gokU1(at)mid(dot)individual(dot)net...
>>
>> On 03/11/12 10:28, Tony Marston wrote:
>>
>>> It *IS* a PHP topic for the simple reason that almost every PHP program
>>> accesses a database, and most databases are designed by the programmer
>>> him/herself.
>>
>> I have to agree with Jerry that it's off topic in this news group, it
>> had been more on topic at alt.php.sql.
>
> Are you saying that it is forbidden to post anything related to SQL on
> this newsgroup? What about HTML? CSS? JavaScript?
>
This is not a SQL newsgroup. Neither is it a HTML, CSS or Javascript
newsgroup. But you don't know the difference, so you don't understand why.
>> Something that people dislike here and most other usergroups is that
>> promotion of your own site and most of the time it's done in a
>> usergroup it's quite low quality or even requiring payment to access
>> the low quality information.
>
> I pointed to a separate article for the simple reason that it was too
> large to post here. I was not saying "please visit my site which
> contains lots of stuff".
>
You spammed your site.
>> A better way to do is to start a discussion and show your skills in
>> there without promoting a website of self-interest.
>
> If you can't be bothered to read the article and respond intelligently
> to the points raised in it then don't respond at all.
>
And if you can't be bothered to post your points in an on-topic
newsgroup, then don't post at all.
I'm not the only one telling you it's off topic. Why don't you figure
it out - this is not your personal newsgroup for posting whatever YOU
feel is on topic. This newsgroup has a charter, and that charter
defines what is on topic or not.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179526 is a reply to message #179520] |
Sun, 04 November 2012 15:45 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 04/11/12 11:09, Tony Marston wrote:
> "J.O. Aho" wrote in message news:afk82dF3gokU1(at)mid(dot)individual(dot)net...
>>
>> On 03/11/12 10:28, Tony Marston wrote:
>>
>>> It *IS* a PHP topic for the simple reason that almost every PHP program
>>> accesses a database, and most databases are designed by the programmer
>>> him/herself.
>>
>> I have to agree with Jerry that it's off topic in this news group, it
>> had been more on topic at alt.php.sql.
>
> Are you saying that it is forbidden to post anything related to SQL on
> this newsgroup? What about HTML? CSS? JavaScript?
Yes, HTML, CSS and JavaScript are also off topic here, as they don't
have anything to do with the PHP. Try to see the different usergroups as
sorting mail, you don't mix your business mail with your private, as it
makes it easier to find the right mail when you are looking for a mail
from a friend with his vacation photos.
For this reason a post in comp.lang.php may be on-topic, while it's
off-topic in alt.php.sql (and the other way too) and there are times
when a post can be on-topic on both or none. It's up to the poster to
keep it on-topic and promoting a site is in general off-topic on most
usergroups.
>> Something that people dislike here and most other usergroups is that
>> promotion of your own site and most of the time it's done in a
>> usergroup it's quite low quality or even requiring payment to access
>> the low quality information.
>
> I pointed to a separate article for the simple reason that it was too
> large to post here. I was not saying "please visit my site which
> contains lots of stuff".
You kind of did that, as you started a new thread and promoted your site
(article), it had been a lot different if there had been someone asking
how to make a good database design on a database related usergroup, then
you could have posted your link and said here you can read some stuff I
have discovered.
>> A better way to do is to start a discussion and show your skills in
>> there without promoting a website of self-interest.
>
> If you can't be bothered to read the article and respond intelligently
> to the points raised in it then don't respond at all.
You didn't post to increase the number of visitors to IBMs "Approach's
database design and how to use a relational database", but to your own
site and in the completely wrong usergroup.
I'm not replying to your articles content, but trying to explain why
people dislike what you did.
--
//Aho
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179528 is a reply to message #179521] |
Sun, 04 November 2012 20:37 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sun, 4 Nov 2012 10:11:42 -0000, Tony Marston wrote:
> "M. Strobel" wrote in message news:afl0ueF96jhU1(at)mid(dot)uni-berlin(dot)de...
>
>> Sorry, I forgot all since I managed DB2 in the early nineties. Forgot
>> all from 15 years as IT trainer. Forgot more than others ever knew :-)
>>
>> /Str.
>
> So even with all those years of experience you are still unable to
> reply intelligently and constructively to the 14 points I raised in my
> article?
Can, could, won't address irrelevant points in this discussion. It's
your *behavior* at issue.
--
The light at the end of the tunnel is not an oncoming train.
It is muzzle-flash.
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179641 is a reply to message #179492] |
Thu, 15 November 2012 14:49 |
Erwin Moller
Messages: 228
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 11/2/2012 8:12 AM, Tony Marston wrote:
> When you design your database for your PHP application are you aware
> that some of your design decisions can have a detrimental effect when it
> comes to writing the code to access that database? I have been designing
> databases, and building the applications which use them, for several
> decades, and I have learned over the years that there are some design
> decisions which help the developer while others do nothing more than
> hinder. I have produced a document which lists 14 of these “hindrances”
> which can be read at
> http://www.tonymarston.net/php-mysql/database-design-ru-novice-ninja-or-nin compoop.html
>
>
> Do you agree with my opinions? Can you think of any more questionable
> design decisions which can be added to this list?
>
I see a lot of negative responses, and I don't think you deserved that.
Personally I don't think it was a bad piece at all.
(People might have a point complaining you are spamming your own site,
but most of your advice is sound.)
Anyway, to the content: As for your basic rules: They all look pretty OK
to me, and I always design databases like that these days (I learned it
the hard way to follow these rules).
I have one additions concerning PKs:
If I apply a PK on a table (and I almost always want a PK) I always
simply add a column for this purpose and use a SERIAL.
I never saw a good reason to use a column that holds text, or a blob, or
something else as the PK.
I also had bad experiences in the past when I thought I was smart and
decide what column(s) could be primary keys.
Quite often it is hard to tell if some value can change.
for example: area-code for a city, a social security number, etc. I
don't expect them to change, but who knows what the next government will
decide? ;-)
My advice: Avoid all annoyances later and simply add an id column for
this purpose.
It is no big deal and you are always sure you have well-chosen PK.
I, like most programmers, hate it when you are not 100% sure something
will suffice in the future. A SERIAL PK will give you this assurance.
It is also very fast (number are much easier compared than text), and it
is small, bytewise.
The drawback is of course it adds a little more data, but I don't care
for that: a small price to pay to be sure you have always a simple way
of referencing a row.
The only times I don't use a SERIAL is when storing many-to-many
relationships.
For example, when you store many-to-many relationships in a coupletable
(not sure if that is the right word):
eg, a table that links users to their favorite food:
CREATE TABLE tbluserfavfood(
userid integer references tbluser(userid),
favfoodid integer references tblfavfood(favfoodid)
)
In this case you want it for ANY userid to be possible to be stored
along ANY favfoodid. Also, one user can store many different favfoodids,
and one favfoodid can be referenced by many different userids.
(Well, you know what many-to-many means I guess.)
The only thing that doesn't make sense in this table is identical rows.
Only in such cases I use the two columns themselfs as PK, and don't add
a SERIAL.
But you might as well add only a UNIQUE CONSTRAINT on them, and NOT
define a PK.
That's all. I think you wrote a pretty good piece, and wanted to give
some constructive criticism after all the flames you got.
Regards,
Erwin Moller
--
"That which can be asserted without evidence, can be dismissed without
evidence."
-- Christopher Hitchens
|
|
|
|
| Re: Bad database design can cause unnecessary coding [message #179651 is a reply to message #179641] |
Thu, 15 November 2012 18:03 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Thu, 15 Nov 2012 15:49:46 +0100, Erwin Moller wrote:
> The only times I don't use a SERIAL is when storing many-to-many
> relationships.
> For example, when you store many-to-many relationships in a coupletable
> (not sure if that is the right word):
> eg, a table that links users to their favorite food:
> CREATE TABLE tbluserfavfood(
> userid integer references tbluser(userid),
> favfoodid integer references tblfavfood(favfoodid)
> )
Terms common for this are map table, junction table, and intersection
table. There are others, but these three are the most common among
people I work with.
--
The true sysadmin does not adjust his behaviour to fit the machine. He
adjusts the machine until it behaves properly. With a hammer, if necessary.
- Brian in the Monastery
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]