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