| 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:
|
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
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]