Re: [PHP] Web dev, DB and "proper db design".

"Tamara Temple"  wrote in message 
news:[email protected]...
>
>
>On Jul 4, 2013, at 8:02 AM, Jim Giner <[email protected]> wrote:
>
>> On 7/4/2013 6:42 AM, Richard Quadling wrote:
>>> Hi.
>>>
>>> I've just had a conversation regarding DB, foreign keys and their 
>>> benefits.
>>>
>>> I was told "I've never worked on a web application where foreign keys 
>>> were
>>> used in the database".
>>>
>>> As someone who has spent 25 years working on accounting/epos systems on 
>>> MS
>>> SQL Server (yep, windows) and now in a web environment and hearing the
>>> above, ... well, ... slightly concerned.
>>>
>>> So, in the biggest broadest terms, what do you lot do?
>>>
>>> DBs with no foreign keys (constrainted or not).
>>> ORM builders with manual definition of relationships between the tables.
>>> Inline SQL where you have to just remember all the relationships.
>>> Views for simple lookups? How do you handle updatable views (does mysql
>>> support them?)
>>> etc.
>>>
>>> Is there a difference in those in 'startups' and web only situations, or
>>> those doing more traditional development (split that as you like - I'm 
>>> just
>>> trying to get an understanding and not go off on one!).
>>>
>>> No definitive answers, and I hope I get some wide experiences here.
>>>
>>> Thanks for looking.
>>>
>>> Richard.
>>>
>> I"m going to guess that your source of such drivel never learned about 
>> such things.  Probably thinks that a 'key' has to be defined as such in 
>> the db, whereas we know what a FK really is.
>>
>> Don't worry.  As a former big iron guy and then a c/s guy and now a (new) 
>> web guy, things haven't changed.
>>
>> -- 
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
>So, like Jim, I'm just going to speculate your correspondent has never 
>actually designed
>anything very interesting. I can't really imagine how one does not use 
>foreign keys,
>unless one does the entire relationship mapping between tables in the 
>source… what
>a waste that would be.

"Tamara Temple"  wrote in message 
news:[email protected]...
>
>
>On Jul 4, 2013, at 8:02 AM, Jim Giner <[email protected]> wrote:
>
>> On 7/4/2013 6:42 AM, Richard Quadling wrote:
>>> Hi.
>>>
>>> I've just had a conversation regarding DB, foreign keys and their 
>>> benefits.
>>>
>>> I was told "I've never worked on a web application where foreign keys 
>>> were
>>> used in the database".
>>>
>>> As someone who has spent 25 years working on accounting/epos systems on 
>>> MS
>>> SQL Server (yep, windows) and now in a web environment and hearing the
>>> above, ... well, ... slightly concerned.
>>>
>>> So, in the biggest broadest terms, what do you lot do?
>>>
>>> DBs with no foreign keys (constrainted or not).
>>> ORM builders with manual definition of relationships between the tables.
>>> Inline SQL where you have to just remember all the relationships.
>>> Views for simple lookups? How do you handle updatable views (does mysql
>>> support them?)
>>> etc.
>>>
>>> Is there a difference in those in 'startups' and web only situations, or
>>> those doing more traditional development (split that as you like - I'm 
>>> just
>>> trying to get an understanding and not go off on one!).
>>>
>>> No definitive answers, and I hope I get some wide experiences here.
>>>
>>> Thanks for looking.
>>>
>>> Richard.
>>>
>> I"m going to guess that your source of such drivel never learned about 
>> such things.  Probably thinks that a 'key' has to be defined as such in 
>> the db, whereas we know what a FK really is.
>>
>> Don't worry.  As a former big iron guy and then a c/s guy and now a (new) 
>> web guy, things haven't changed.
>>
>> -- 
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
>So, like Jim, I'm just going to speculate your correspondent has never 
>actually designed
>anything very interesting. I can't really imagine how one does not use 
>foreign keys,
>unless one does the entire relationship mapping between tables in the 
>source… what a
>waste that would be.=

I was designing and building database applications before relational 
databases became popular, and in those old hierarchical and network 
databases there were no such things as foreign key constraints. Database 
integrity had to be handled in the code, which is what we did. When I 
started working with relational databases there were features of the 
language, such as FK constraints, which were rarely used even though they 
were there. When I started working on web applications the predominant 
database was MySQL, and the vast majority of ISPs would only offer MyISAM 
and not Innodb, and MyISAM does not support FK constraints.

There are some programmers who complain that without foreign keys being 
defined in the database, how do you recognise relationships to build into 
SELECT statements? Simple. Every primary key is in the format 
‘<tablename>_id’, so if you see this format in a table, and <tablename> is a 
different table, then it’s a foreign key. This is actually easier to 
identify than having to look in a separate place.

Remember that there is a difference between a foreign key and a foreign key 
constraint. Foreign keys can exist without constraints, so saying that you 
cannot use a field as a foreign key in a SELECT statement without having a 
constraint defined in the database is just plain wrong.

-- 
Tony Marston

http://www.tonymarston.net
http://www.radicore.org