2 Replies Latest reply on Oct 13, 2011 9:32 AM by philmodjunk

    Split Single Table into More Than One



      Split Single Table into More Than One


      I have a database with several thousand records all stored in one table ("Stock").  It is used for tracking inventory for a furniture store.  The fields include "Manufacturer", "Item Number", "Description", "Invoice Date", "Cost", "Customer", "Sale Date", "Selling Price", "On Floor?", "Sales Rep", "Record Number" and a few others (some calculated like "Profit Margin").

      Is there a reason that this should be structured as more than one table? 

      If so, can I use the data in the original table to populate the new table(s) easily?

      Please also let me know if there is some online tutorial for FM that would explain some of these more basic concepts.




        • 1. Re: Split Single Table into More Than One

          It is generally more efficient to have separate tables to keep track of the different things like Products, Sales Rep, Customers.  For the sale of an item you create a record in another table (commonly called a join table) that just contains references to the ProductID, CustomerID and SalesRepID along with the sale date, etc.

          This makes it easy to track sales by product, sales rep, customer, date ranges, etc.

          It is not too difficult to split a single table into separate tables, though it will take some time.  Working with a copy of your original file - You can import from the original into a new table, then just select the fields related to the Products such as record number, item number, description, cost....  Once that import is complete rename the Table to Products, go though the records to elimiate duplicates and make sure everything looks right.   Then repeat the process for Customers, Sales Reps, etc. importing the appropriate fields for each.  Once that is done you can go into the ManageDatabase/relationships tab and set up the relationships between the tables.

          • 2. Re: Split Single Table into More Than One

            Here's a simple demo file created by Comment that you may want to examine:  http://fmforums.com/forum/showpost.php?post/309136/