US Data Export wrote:
>> -----Original Message-----
>> From: Martijn Tonies [mailto:m.tonies@stripped]
>> Sent: Friday, November 14, 2008 10:44 AM
>> To: 'mysql'
>> Subject: Re: normalised designs: customer database
>>>> 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
>>>> the presence of that key
>>> [JS] I'm not sure why you need a foreign key. Surely you won't be
>>> 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- ...
>> 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.
> 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?
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.