> >>>> 3) create the customer table with a FK for people and a FK for > >>>> companies, and decide on the customer type in the application based > >>>> > >> on > >> > >>>> the presence of that key > >>>> > >>>> > >>> [JS] I'm not sure why you need a foreign key. Surely you won't be > >>> > >> entering > >> > >>> customers using the MySQL CLI client on a routine basis, so your user > >>> interface could (and should) be responsible for checking the data. > >>> > >> Ehm, no, if it's possible, put the constraints -on the database- ... > >> Never > >> ever rely on the application alone to enforce data consistency. > >> > >> > > [JS] I understand your point, but in real life that can lead to a user > > seeing ugly, incomprehensible error messages.
Error messages should be handled, obviously, but that is besides the point. > > What I do, in many cases, is provide a dropdown whose values are populated > > from a table of possible values. I suppose you could use that same table to > > enforce foreign key constraints as well, but isn't the effect the same? No, including this part below: > In any event, all data and constraints will be checked in the > application, prior to inserting/updating the database. > It would however be nice to have the db error out in case the > application misses something. It's an awful lot of trouble to weed out > badly inserted or updated records in case of mistakes. > On top of that, the application is only under my control until I become > a millionaire and hire a monkey to code it for me. Your constraint exist because of the programmer making mistakes in his code. Additionally, add-on applications like imports, 3rd party applications etc can make things much harder. When the constraints are on the database, you're at least sure the consistency is OK. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase SQL Anywhere, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
