1 Reply Latest reply on Apr 2, 2013 2:07 PM by gcatnine

    Newbie working on the DB design


      I'm currently working on my first DB!!! I can't get my head around one scenario.


      I'm trying to track the equipment we sell and install on boats. Each boat has a shipID primary key and a Name as well as some other attributes. The problem I have is the "name" may change over time, so I've thought about adding another column as "exname"; so when the name changes I just update with new name an move original name to exname!


      But.... Often a boat could have 3 4 5 exnames over time and this would break the "normal form" rules I've been reading about.


      I need to be able to query a "name" and return shipID's for both current and exnames!


      Also multiple shipID's could have the same "name"


      Thank you in advance for any advice.


      USING FMP11



        • 1. Re: Newbie working on the DB design

          You can use another table “Table_Ship_Names”, with a fk_shipID (foreign key)where to store all the names,

          You create a field in this table to distinguish the actual name (1 = actual name)

          To get all ship names (old and actual):

          Table_Boat::shipID is related to Table_Ship_Names::fk_shipID


          To get the actual name you need a second TO (Table occurrence):

          Table_Boat::shipID is related to Table_Ship_Names 2::fk_shipID


          Table_Boat::constant_one is related to Table_Ship_Names 2::Actual_Name


          Table_Boat::constant_one is a global calculated field = 1 that return a number


          With a portal that refer to the first TO of Table_Ship_Names you get all the names