Mark, The current scenario sounds like: "Each teacher can be assigned one or more flights, but each flight can have one and only one teacher". The new scenario sounds like it would be "Each teacher can be assigned one or more flights and each flight can have one or more teachers."
To create this data model, you need to create a junction table that would have two columns: teacher_id and flight_id. The primary key in this junction table would be the combination of teacher_id and flight_id (since it would be ridiculous to have the same teacher assigned to the same flight multiple times). You could also put other information in the junction table such as an order/preference number. This would accommodate if a teacher has a preference for a given flight. The data model would be: Flights ----------------- flight_id flight_name etc,etc Teachers ------------------- teacher_id teacher_name etc, etc TeachersFlights ---------------------- teacher_id flight_id order_number Hope this helps, Peter On 3/23/2012 7:29 PM, Mark Phillips wrote:
I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark --------------------------------------------------- PLUG-discuss mailing list -PLUG-discuss@lists.plug.phoenix.az.us To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
--------------------------------------------------- PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
