php-general Digest 5 Jul 2013 04:38:25 -0000 Issue 8284
Topics (messages 321522 through 321527):
Re: Web dev, DB and "proper db design".
321522 by: Tony Marston
321523 by: Jim Giner
321524 by: Lester Caine
321525 by: Andy McKenzie
321526 by: Tamara Temple
321527 by: musicdev
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
"Richard Quadling" wrote in message
news:CAKUjMCWJ4wiUO904OvYkS53Fsg4PPXa=qbokcvhwfemcpkp...@mail.gmail.com...
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.
There is a difference between having a field which is used as a foreign key
and having a foreign key constraint defined in the database. Remember that
foreign keys can be used in SELECT statements without there being a FK
constraint. Constraints are only used in INSERT/UPDATE/DELETE operations,
and never used for SELECTs
You cannot have relationships in a database without foreign keys, but you
can have foreign keys with constraints.
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
--- End Message ---
--- Begin Message ---
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.
--- End Message ---
--- Begin Message ---
Richard Quadling wrote:
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!).
Depends if you consider MySQL is any use as a real database :)
Building the constraints and many other core database features into the code was
essential before MySQL had many of the features that real databases have had
from the start ...
--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
--- End Message ---
--- Begin Message ---
I think it depends on the application.
A lot of small web apps simply don't need any kind of normalization, and it
really does make sense to put everything in one table, or a couple of
unlinked tables. Therefore, on those apps, there's no need for foreign
keys. For instance: I used one in-house application that had two tables:
A list of authorized users with their passwords and a couple of other
things (administrative role, security level, things like that), and a table
that contained information about computers used in the the department.
Sure, some of the users had a computer in the department, but not all of
them. So there was really no need to link the tables. And since the
information stored about the computers was stuff like owner, name, MAC, and
IP address, there was no need for foreign keys in that table either: it
just wouldn't make sense.
Now, anything much more complex then that there's going to be some value in
using foreign keys, whether formally (constraints set in the DB) or
informally (constraints imposed in the web interface), but it's quite
possible the guy had never worked on something where they were needed.
-Andy
.
On Thu, Jul 4, 2013 at 10:20 AM, Lester Caine <les...@lsces.co.uk> wrote:
> Richard Quadling wrote:
>
>> 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!).
>>
>
> Depends if you consider MySQL is any use as a real database :)
> Building the constraints and many other core database features into the
> code was essential before MySQL had many of the features that real
> databases have had from the start ...
>
> --
> Lester Caine - G8HFL
> -----------------------------
> Contact -
> http://lsces.co.uk/wiki/?page=**contact<http://lsces.co.uk/wiki/?page=contact>
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media -
> http://rainbowdigitalmedia.co.**uk<http://rainbowdigitalmedia.co.uk>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--- End Message ---
--- Begin Message ---
On Jul 4, 2013, at 8:02 AM, Jim Giner <jim.gi...@albanyhandball.com> 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.
--- End Message ---
--- Begin Message ---
On Thu, Jul 4, 2013 at 4:20 PM, Tamara Temple <tamouse.li...@gmail.com>wrote:
>
> On Jul 4, 2013, at 8:02 AM, Jim Giner <jim.gi...@albanyhandball.com>
> 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.
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
I completely agree.
--- End Message ---
