I run a database with a lot of tables used only to reference two other tables. Sometimes they use a couple other fields also.
After some testing, I preferred to use multiple selects (one per relationship table) since the result of the query was getting way to huge when having 4-5 relationship tables to query for the display of the record and doing them in one huge select. I store/print the results of the query to each table. You can automatically have all children deleted when a parent is deleted, but in my case I prefer to do it "manually". My script checks for related data and kindly asks the user to either first delete the related data or asks the user if he want's the related data automatically deleted (altough there are a lot of authentification/rights/tests to see if the user has the right to delete records). Some tables can be deleted and their corresponding relationship table row(s) are deleted, keeping the related data, which might be related to other tables (humm a little complicated sentence here). I'm not sure exactly what information you're looking for, but if you want some details on my database I'll be glad to send drawings of the tables and relationships. My database took months to build, reviewing it endlessly for finding new little improvements to be done. I think that right now it's pretty in it's best, and there is little to be done to have a better data integrity. Also, theer is a book called "relational database design for mere mortals" (if my merory doesn't fail) which is REALLY a good book to read about relationnal database design Etienne "Vuillemot, Ward W" wrote: > I would like to know how people handle foreign keys with mysql. > Has anyone ever created a relationships table for use with other tables? If > so, what types of fields did you find necessary to define the relationships? > How did you handle parent-child relationships -- when parents are deleted > all their children must also be deleted. . .I would really to learn what > types of experiences have had. > > Thanks in advance, > Ward
