Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #7423 > unrolled thread

Bad database design can cause unnecessary coding

Back to article view | Back to comp.lang.php

Contents

  Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-02 07:12 +0000
    Re: Bad database design can cause unnecessary coding "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-11-02 09:53 +0100
      Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-03 09:14 +0000
        Re: Bad database design can cause unnecessary coding "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-11-03 18:01 +0100
          Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-03 15:03 -0400
          Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-04 10:11 +0000
            Re: Bad database design can cause unnecessary coding "Peter H. Coffin" <hellsop@ninehells.com> - 2012-11-04 14:37 -0600
        Re: Bad database design can cause unnecessary coding "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-11-03 19:00 +0100
          Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-04 10:18 +0000
            Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-04 07:29 -0500
    Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-02 06:48 -0400
      Re: Bad database design can cause unnecessary coding Goran <goran@nospam.com> - 2012-11-02 12:34 +0100
        Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-02 07:48 -0400
          Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-03 09:28 +0000
            Re: Bad database design can cause unnecessary coding "J.O. Aho" <user@example.net> - 2012-11-03 10:56 +0100
              Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-04 10:09 +0000
                Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-04 07:32 -0500
                Re: Bad database design can cause unnecessary coding "J.O. Aho" <user@example.net> - 2012-11-04 16:45 +0100
            Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-03 10:52 -0400
        Re: Bad database design can cause unnecessary coding The Natural Philosopher <tnp@invalid.invalid> - 2012-11-02 11:56 +0000
      Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-03 09:17 +0000
        Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-03 10:54 -0400
          Re: Bad database design can cause unnecessary coding Luuk <luuk@invalid.lan> - 2012-11-03 16:06 +0100
            Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-03 11:25 -0400
    Re: Bad database design can cause unnecessary coding Denis McMahon <denismfmcmahon@gmail.com> - 2012-11-02 11:13 +0000
      Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-03 09:31 +0000
        Re: Bad database design can cause unnecessary coding Denis McMahon <denismfmcmahon@gmail.com> - 2012-11-03 13:08 +0000
          Re: Bad database design can cause unnecessary coding Jerry Stuckle <jstucklex@attglobal.net> - 2012-11-03 10:55 -0400
          Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-04 10:04 +0000
    Re: Bad database design can cause unnecessary coding Luuk <luuk@invalid.lan> - 2012-11-03 11:04 +0100
      Re: Bad database design can cause unnecessary coding "Tony Marston" <TonyMarston@hotmail.com> - 2012-11-04 10:30 +0000
    Re: Bad database design can cause unnecessary coding Erwin Moller <erwinmollerusenet@xs4all.nl> - 2012-11-15 15:49 +0100
      Re: Bad database design can cause unnecessary coding "Peter H. Coffin" <hellsop@ninehells.com> - 2012-11-15 12:03 -0600

Page 2 of 2 — ← Prev page 1 [2]

#7436

"Jerry Stuckle"  wrote in message news:k708e6$fjt$1@dont-email.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-nincompoop.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 

[toc] | [prev] | [next] | [standalone]

#7443

On 11/3/2012 5:17 AM, Tony Marston wrote:
> "Jerry Stuckle"  wrote in message news:k708e6$fjt$1@dont-email.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-nincompoop.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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#7445

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?

[toc] | [prev] | [next] | [standalone]

#7446

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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#7426

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

[toc] | [prev] | [next] | [standalone]

#7438

"Denis McMahon"  wrote in message news:k709tb$h2i$1@dont-email.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 

[toc] | [prev] | [next] | [standalone]

#7441

On Sat, 03 Nov 2012 09:31:15 +0000, Tony Marston wrote:

> "Denis McMahon"  wrote in message news:k709tb$h2i$1@dont-email.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

[toc] | [prev] | [next] | [standalone]

#7444

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@dont-email.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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#7450

"Denis McMahon"  wrote in message news:k734vv$so2$1@dont-email.me...
>
>On Sat, 03 Nov 2012 09:31:15 +0000, Tony Marston wrote:
>
>> "Denis McMahon"  wrote in message news:k709tb$h2i$1@dont-email.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-nincompoop.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 

[toc] | [prev] | [next] | [standalone]

#7440

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-nincompoop.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.

[toc] | [prev] | [next] | [standalone]

#7454

"Luuk"  wrote in message news:vq6fm9-97b.ln1@luuk.invalid.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-nincompoop.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 

[toc] | [prev] | [next] | [standalone]

#7572

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-nincompoop.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

[toc] | [prev] | [next] | [standalone]

#7582

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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.php

csiph-web