0 Replies Latest reply: Sep 12, 2012 3:05 AM by Dino2dy RSS

    App Design table with 2 FK but user picks which one to insert value into

    Dino2dy
      I have a design question.

      I have 3 tables, Equipment, Employees, Shops.

      Now the table Equipment has 2 foreign keys in it for Employees and Shops, EmpId and ShopId.

      Now Equipment is supposed to be assigned. You can assign it to an employee OR to a shop, as some general equipment like shop Sign or Billboard or Table are assigned to objects because if you assign them to people who work there, when they get transferred you have a problem, and they transfer around the shops a lot.

      So I designed an app, not realizing this, that has your typical drop down list for choosing an employee to Assign something to. But now I will have 2 drop down lists potentially.

      But the thing is most times only one will be chosen. Its either or.

      So my question is how could I design that? Any ideas? Keep in mind I am pretty much a novice when it comes to programming. I figure disable one drop down list if the other has a value selected but then the question is what will you put in the foreign key field? I have enabled it to be able to have NULL values in there but not sure if that's good practice.

      I welcome all the help I can get. I really could use it.

      Edited by: Dino2dy on Sep 12, 2012 1:04 AM