The advantage of creating the reltionship, is that it enforces the external logic about the system that the data represents. The dowside, is that anything like this inevitably slows things down, because it requires the database manager to check the things you have stipulated to it before blindly carrying out the actions you request.
This applies to any constraint you apply to your data. For a simpler example, we could, if we wanted, store all the results of a question which has a simple "Yes" or "No" answer in a field of type char (storing either 'Y' or 'N', respectively). It makes more sense, however, to store it in a field of type ENUM('Y', 'N'), or even ENUM 'Yes', 'No'), since a char field will quite happily take a value of 'G' or '$', or any other member of the ASCII characterset without complaining. The penalty is, that the database manager must check what is being given and ensure that it is either a 'Y' or a 'N', before inserting it. This penaly is so vanishingly small, however, that it simply isn't worth dispansing with the ENUM field type.
At a more complex level, it would be much quicker to store all data that you search very frequently on the basis of its date and time values by using UNIX timestamps for all the date and time values in fields of type INT (inserting and retrieving them via the UNIX_TIMESTAMP and FROM_UNIXTIME functions). However, to do so would mean losing MySQL's impressive battery of Date and time-handling functions, as well as causing all your date and time data to become essentially unreadable as date and time data. Any such implementation would therefore be almost worthless since Unix timestamp is one of the date and time formate which MySQL doesn't natively support. The time penalty incurred in using the programmed-in intelligence of the database manager is almost always less than the penalty of storing dates as values which differ from on another by - I think it's something like 84,600 seconds, but I can't be bothered doing the calculation - I'm sure you get my drift... :)
Likewise, the use of foreign keys enforces the external logic of what your data is ment to represent in the real world, and prevents illogical things being done, or the accumulation of redundant data (as an example, it stops you deleting an organiser for an event until you first delete the event, for instance, since your design stipulates that an event without an organiser would throw as major a spanner in the external logic of your application, as would a test for "has this event been deleted?" getting a response of "$").
Take it easy
Dan
|
