> >>>> 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
> > What I do, in many cases, is provide a dropdown whose values are
> > from a table of possible values. I suppose you could use that same table
> > 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.
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Sybase
SQL Anywhere, Oracle & MS SQL Server
Database development questions? Check the forum!