5 Replies Latest reply on Nov 9, 2010 1:05 PM by philmodjunk

    Help With Database Structure



      Help With Database Structure


      Hello I am looking for help on how to structure my database.

      I have many products, each has a unique ID or code...e.g. GH/254/JK. The start of the code (GH) corresponds to a customer. There are tens of customers some with 100's of lines of products. e.g GH/255/HL, GH/256/KL. Lots of the different products have the same contents but different packaging (GH/255/HL has same contents but different packaging to PP/255/HL). 

      I am making a database to display details for each product. SO at a simple level it goes something like this:

      Product Code | Product Name | Description | Packaging

      GG/255/PP | This is the name of the product | This is the description of contents | This is description of contents.

      Originally I did a single table with all these products on which was working OK. I then decided I needed a way to reference the products against each other so I made a second table related to the first. The second table had a Product Code related to the first table's Product Code. I then made lots of columns for each customer so:

      Product Code | GG | HI | PP...etc

      GG/255/PP | GG/255/PP | None | PP/255/PP

      This was to try and reference the products against each other. This broke down when I would then come across PP/255/PP and obviously it wasn't referenced BACK to GG/255/PP. I could constantly try and keep the 2 sets of products up to date with each other but this has already got out of hand and I often find that if I search for products with a GG code AND a PP code (in the PP field) I get ..say 500 products... but if I search for products with a PP code AND a GG code (in the GG field) I get say 492 products...so I have made a mistake somewhere in referencing.

      I can't get my head round a way to keep all these codes referenced up against each other. The one thought I had was to make a products table for EACH customer...and then have a relationship from EACH customer on one table to every other table...Having 50 x 50 relationships = 2500. Way too many.

      Has anyone got any other solutions? 

      I am very grateful for any help. If I have been too vague please ask for more information.

      Many Thanks,


        • 1. Re: Help With Database Structure

          You should have at least these tables:

          Customers----<Products>---Packaging     ( one ----< Many )

          each table should have a field with an auto-entered serial number. Use these fields to link your records. You have other data such as your product code that looks like you could use for this, but don't--if a customer or product code needs to be changed, you don't want that change to break the link to other tables.

          Customers::CustomerID = Products::CustomerID
          Products::PackagingID = Packaging::PackagingID

          Customers::CustomerID and Packaging::PackagingID are auto-entered serial numbers.
          Products::CustomerID and Products::PackagingID are number fields (no auto-enter).

          In customers, you'd have one record per customer and you'd place their customer code (GG in your example) in this record. If you are looking at a layout of products, you can place this field from the related customer table on your layout to display it.

          All products with the same packaging would link by PackagingID to the same Packaging record.

          If you haven't alread, look up Portals in FileMaker help as this is a very useful tool for viewing/editing related records in one to many relationships like you have here. A portal to Products on a customer layout would list all that customer's product records and a portal to products on a packaging layout would list all products that use that packaging--too give too possible uses for a portal.

          • 2. Re: Help With Database Structure

            Hi Phil thanks for your reply.

            I should have mentioned that packaging is more of a description and is different for every single product (even if the product is destined for the same customer) so unless I have misunderstood, the above wouldn't quite work. BUT you have helped solve my problem I think; perhaps you could assess my idea:

            Firstly I will tell you that I have about 3000 products to consider. What defines a product in this instance is BOTH its contents and its packaging. In my case the contents description is used among many products, but the packaging description is different for EVERY product. This may be seem counter intuitive but that is because this company manufactures for many customers. There is a core range of products all coded with RE/###/##. The contents for many of these products are used for other customers so RE/001/TB is used for ACP/001/TB...another customer but who has a unique packaging description. I propose:

            Have 2 tables.

            First table is:

            Contents ID (e.g. RE/001/TB) | Contents Description |

            Second table is:

            Products ID (e.g. ACP/001/TB) | Product Name | Contents::Contents ID | Packaging |

            With this 2 table structure I would be able to change all contents by changing the contents in the contents table. I would also be able to have a reference system which works both ways. e.g. Look for products which have an ACP product ID and which have RE contents. I could also see all products which have the RE/001/TB contents by searching for that as well...and a list of codes may come up ACP/001/TB, CE/001/TB, FE/001/TB, etc.

            I would be very grateful for any comments, especially if you see a gaping hole in my plan.

            Many Thanks,


            • 3. Re: Help With Database Structure

              I did misread your first post. I thought that multiple products had the same packaging--like they all went into the same size box or something Wink

              I see this is not the case, rather that the product labeling, clamshell etc makes for a unique item that may enclose the same product as another packaging configuration does.

              I'd still use three tables here, so that you don't have to repeat all the product info for each different packaging configuration.


              Customer::CustomerID = Packaging::CustomerID
              Packaging::ProductID = Product::ProductID

              • 4. Re: Help With Database Structure


                Thank you for your reply. I have to say that I wasn't understanding your replies at first because you would keep suggesting that I have 3 tables and then i would only see a 2 table relationship. This was because I was reading this reply in my apple mail and for some reason it won't display things which it thinks are HTML tags e.g. <packaging> . But now I am viewing the reply in my web browser and I understand better.

                i have done the new design which I decided to be a 2 table design ;)

                Assuming this design I have encountered a problem which maybe you can help me fix.

                Contents Table

                Contents ID | Product Contents

                Products Table

                Product ID | Product Name | Contents ID | Contents::Contents | Packaging

                Relationship between Contents IDs.

                The system works so that if I come across say GG/003/XCS and I know it has the same contents as RE/003/XCS, I can just type the RE/003/XCS code into the contents ID, which then interacts with the contents table and realises that it will display a contents. +VE is that if something changes for RE/003/XCS I can simply make this change in the contents table which then updates all other 003/XCS products.

                If a layman were to add a new product I would give them the option of 'existing contents' or 'new contents'. Its fine for the former because its simply a case of entering an existing code in the contents table and seeing the result. My problem is with the latter: If someone (a layman) wants to add a NEW product which doesn't have existing contents then they would have to create a new record for the contents table AND the products table, as well as enter a reference in the Contents ID field of the Products table. I don't want to bombard the user with having to type in the codes lots of times so I would need the following:

                1) User presses new record.

                2) User enters new code for new product which then creates a new record in Contents table AND Products table and at the same time puts a reference in the Contents ID section of the Products table. So 3 things by typing a code once.  Is this possible by relationships and auto enter options? Or would it be a case of scripting? Or not possible at all?

                Very sorry if that didn't make any sense. If it didn't, don't feel compelled to comply :)

                Many thanks.


                P.S. If I've misunderstood my relationships and your 3 table suggestion then please let me know. I am not seeing the benefit for me with a 3 table system but I am not ignorant and am happy to hear an explanation. 

                • 5. Re: Help With Database Structure

                  What happend to your customer data? That was the purpose of the third table. If you don't have any info that you are recording that is specific to one customer then there's no need for the third table. I suspect, however, that you supply multiple products to the same customer, in which case it makes sense to have a customer table where you store data about that customer in a single record for each customer but then link that record to each product that you supply to them.

                  There are quite a few ways to automate your data entry. The simplest and first to implement is to learn how to define a two column value list. Column one is the ID code and column 2 is a name field that helps the use select the correct value. In your example, you might define column 1 to list the Contents ID from the Contents table and Product Contents as the column 2 name field.

                  You also should look into how to use Portals as you can set up a portal so that you can create a new record on your portal and then create a new record in a related table simply by entering data in the bottom blank line of the portal.