OT: SQL Question

[AZ Pete]

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 at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20120323/cd71fa25/attachment.html>