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

    Newbie working on the DB design

    footie11

      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

       

      JF

        • 1. Re: Newbie working on the DB design
          gcatnine

          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

          And

          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