Faced a similar problem here. I have a details table that can hold file detail (like filename, location, etc) for other tables, say videos, ebooks, and musics. The "correct" association would be videos hasOne details, and details hold the FK. But when you add the other two tables, that means details will hold the (nullable) FK for videos, ebooks, and musics. Not good.
So I reversed the database structure. Now when you add a record in videos, ebooks, or musics, it will create the corresponding details record, and associate it with details_id FK found in each table. I know this looks silly, but this is the best, most flexible design I can create. If any other table needs an association with details, the only table that needs to be changed would be the new table. Nothing else in the database needs changing. This just doesn't happen if you have the FK in the details table. Anybody with a better database solution? Oh, and constraint checking; such as making sure that a single details record has only one association with either videos, ebooks, or musics, should be done in the business logic. The database platform simply does not have that kind of information (or would be quite hard to implement with SQL). -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mikee Freedom Sent: 10 Nopember 2006 10:29 To: [email protected] Subject: Location of foreign key Afternoon all, This may be a stupid question but I would like to know as it would make life somewhat simpler at the end of the day. In a hasOne relationship address: e.g. User hasOne UserDetail Where the users table includes core user information and the user_details table includes additional information (just an example). Normally I would put the user_id in to the user_details table yes? and the hasOne association set in the User model would always return the UserDetail data. What if I wanted a single table to act as the one to many tables (if that makes sense). For example: donations orders payments will be three different methods that a user can pay money on a particular site I'm working on. Each of these will be using the same payment gateway which has supplies certain information that I would like to record. I like to record this in a seperate table in case I need to switch gateways at any point. So, I would usually have just the one table, like so: orders -> recording amount paid, and other information order_gateway_details -> recording particular gateway stuff for this order [bad table names I know but it's late on a friday afternoon] now i would be forced to create three seperate gateway_details tables. order_gateway_details donation_gateway_details payment_gateway_details is it acceptable to put the id of the gateway details in to the orders, donations, and payments table? that way I could record all of the details in the one table where they belong. yet still reference them from their respective payment methods. I would be reversing the relationship I realise - gateway_details doesn't ever have one donation (etc). but it would make life easier... this is not cool is it? penny for your thoughts. cheers, mikee --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
