Re: [PHP] Web dev, DB and proper db design.
Tamara Temple wrote in message news:557a0092-2b7d-49f4-ae3d-593968dd3...@gmail.com... 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. Im 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:557a0092-2b7d-49f4-ae3d-593968dd3...@gmail.com... 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. Im 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
Re: [PHP] Web dev, DB and proper db design.
Tony Marston wrote: 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. This was my point about MySQL ... what database were you using before MySQL came along? 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. Certainly in some instances then managing everything in code makes sense. Cross database working for instance. So everything works the same for each engine. But the main advantage of adding constraints in the database is that it protects the foreign entries from being deleted while they are still in use. One of the problems I end up having to recover is where a hickup in the PHP or user side of things has deleted an entry, or more usually the whole table! In this case a properly implemented constraint would have prevented the problem. While using a constraint to manage the deletion of detail elements has a place in reducing traffic between program and datbase, the protection of detail elements is the more useful reason for maintaining them. -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Web dev, DB and proper db design.
Lester Caine wrote in message news:51d6987c.9050...@lsces.co.uk... Tony Marston wrote: 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. This was my point about MySQL ... what database were you using before MySQL came along? I used ORACLE and SOLID, and before that Hewlett Packard's IMAGE (network DB) and Data General's INFOS (hierarchical DB). 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. Certainly in some instances then managing everything in code makes sense. Cross database working for instance. So everything works the same for each engine. But the main advantage of adding constraints in the database is that it protects the foreign entries from being deleted while they are still in use. One of the problems I end up having to recover is where a hickup in the PHP or user side of things has deleted an entry, or more usually the whole table! In this case a properly implemented constraint would have prevented the problem. While using a constraint to manage the deletion of detail elements has a place in reducing traffic between program and database, the protection of detail elements is the more useful reason for maintaining them. In my framework I define relationships and any constraints in my data dictionary, and these are enforced by a standard module in the framework. This means that any RESTRICTED constraints can be detected in the code without executing a DELETE and having it abort. -- Tony Marston http://www.tonymarston.net http://www.radicore.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Web dev, DB and proper db design.
Tony Marston wrote: Certainly in some instances then managing everything in code makes sense. Cross database working for instance. So everything works the same for each engine. But the main advantage of adding constraints in the database is that it protects the foreign entries from being deleted while they are still in use. One of the problems I end up having to recover is where a hickup in the PHP or user side of things has deleted an entry, or more usually the whole table! In this case a properly implemented constraint would have prevented the problem. While using a constraint to manage the deletion of detail elements has a place in reducing traffic between program and database, the protection of detail elements is the more useful reason for maintaining them. In my framework I define relationships and any constraints in my data dictionary, and these are enforced by a standard module in the framework. This means that any RESTRICTED constraints can be detected in the code without executing a DELETE and having it abort. My own database management framework pre-dates PHP switching from dBase originally, to Interbase and then Firebird as it was open sourced (just as we had paid for a large number of Interbase licenses :( ). So I've grown up using the tools in the database, but have to back pedal some of the time where a cross database solution is required. I'm much more comfortable with getting the database to manage things and will often drop triggers in rather than coding that in PHP. So proper db designis probably still geared around what we are used to rather than any had and fast rules? -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Web dev, DB and proper db design.
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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Web dev, DB and proper db design.
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=**contacthttp://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.**ukhttp://rainbowdigitalmedia.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Web dev, DB and proper db design.
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. Im 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
Re: [PHP] Web dev, DB and proper db design.
On Thu, Jul 4, 2013 at 4:20 PM, Tamara Temple tamouse.li...@gmail.comwrote: 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. Im 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.
