2 Replies Latest reply on Jul 23, 2015 1:26 PM by philmodjunk

    Proper set up of a "Product" table (or tables)



      Proper set up of a "Product" table (or tables)



      For the CRM solution I am creating, there are many products that eventually have to be selected in an invoice. There are about 7 different types of products, so what I have done so far is create seven separate tables for each of the product types, which contain the specific fields tailored to each product type. Is this the proper way to do it, or will I discover that I have shot myself in the foot later on down the road? I figured this would be the best option because in terms of displaying the data, having a layout for each table and designing a series of buttons to navigate between layouts seemed like it would be the cleanest and most efficient. 



        • 1. Re: Proper set up of a "Product" table (or tables)

          All the products should probably go in one table. Unless their attributes are so different that they need their own table for their type.

          In other words.  If you sell cars and washer machines, I would put them in 2 tables.  But if everything you sell really only has a product type, description, wholesale price, retail price, I would put them in the same table.  If it's the same products with different vendors, then you may want to add a vendor table

          For creating invoices, you should follow the basic Invoice relationship setup


          LineItems is a join table that you will put in a portal on the Invoice layout.  LineItems will look-up products to add to portal.  Customers will just be a dropdown of customer names to populate the invoice.

          • 2. Re: Proper set up of a "Product" table (or tables)

            Even if selling Cars and Washing machines, you should have a single table of at least the price and product ID as that makes things much easier to work with. If you then need to record other info that is very different for different categories of products (or services), use related detail tables linked to your single table of product ID's and prices.