0

Table or Sub-Table?

Hi,

I'm building a database for my company we do production and sales and I'm confused when to make a related table and when to make a sub-table. I understand when I make a table invoice - with subtable Invoice lines (items).

I have made the following database:

 I am worried to have too many sub-tables in sub-tables.

Just a little background we work with 3 different product groups: finished products, half finished products and raw supplies. I want to use the database to keep inventory of our supplies and products (this works) and when we have an order to see if I have:

a) enough finished products to fill the order and/or
b) combination of half finished products and raw supplies to fill the order and/or
c) enough raw supplies to fill the order

I am now at the point where I want to make a table with the possible combinations to create a half products and finished products, so that I can automate the system to tell me if and how I can fill an order and see if I have enough products or need to order more raw supplies.

Any advice or help will be appreciated. 

18 replies

null
    • John_Halls
    • 3 mths ago
    • Reported - view

    Hi  I would be asking a simple set of questions that determine the relationships.

    Can a Contact have more than 1 invoice? Yes

    Can an invoice relate to more than one Contact? No

    So we now know we have a 1:N relationship between Contacts and Invoices. Now ask

    Does an Invoice HAVE to have a Contact? Yes

    We now also know that Invoices is a sub-table of Contacts (Composition is ON) which prevents an Invoice being an orphan (one with no Contact)

    I would also be careful about splitting out Parts, WIP and Finished Products into their own tables. If, on the whole, they have similar properties then it's more than likely that they all belong in the same table. For these I'd say they all live somewhere, have a value, can be counted, have a SKU, etc so they belong in the same table.

    I always aim to have as few tables as possible

    Hope that helps. Good luck and always feel free to come back. Maybe share your model again soon?

    Regards John

    • Michael_Cornelissen
    • 3 mths ago
    • Reported - view

    Thank you John,

    I get what you are saying, and I'm glad that I'm on the right track. The confusion starts when combining data from multiple tables and sub-tables.

    My idea always starts with what the easiest way to enter the data is. Once it's entered it's the way data is related and how the data can show you the results or answers you need.

    If any of you want to contribute or reply, please do so. 

    • John_Halls
    • 3 mths ago
    • Reported - view

    Let’s start with Trades. Can you explain what they are? Thanks

      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      Sorry, this was just a quick test DB, not the best naming habits. Trades are the Invoice Items that relate to the products,  mostly Finished Products.

      Every Invoice line Item (Trade) is related to a Work Order, when I make up the WO-Items (related to Products), I can see what we have in stock for Half-Finished Products or Raw Supplies and fulfill the WO-Item accordingly.

      • Fred
      • 3 mths ago
      • Reported - view

      Seems like Work Orders doesn't need to be a child of Invoice. Invoice is what the goes out to the client, and work orders are internal. Different data needs to be tracked for each side.

      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      I already started over again, and was thinking the same. Thanks for confirming my thoughts.

    • Fred
    • 3 mths ago
    • Reported - view

    Don't worry about too many tables. 😂 The important thing is if the data is different enough to need a new table. Most people don't make enough tables. If you find yourself making field names with a number after it to track different instance of similar data. Then you need a new table. Like the employees assigned to a job. Even if it is typically one or two employees per job, most people would create two fields employee1 and employee2. But even this case you would want to create a child table to jobs called workers.

      • John_Halls
      • 3 mths ago
      • Reported - view

       Ah, yes. Fred is absolutely correct here. When I said I try and use as few tables as possible, I didn't mean I have Employee1, Employee2 fields in my tables (not that I'm admitting to). I meant I'd try and use a single table for all my Products, just one for all my Transactions, one for Customers and Suppliers (called Parties), etc, with sub-type fields to be able to create subsets of data.

      • Fred
      • 3 mths ago
      • Reported - view

      Was not implying you made any of DB mistakes that I made.😉 I agree that you should try to use as few tables as possible. Some go the other way and create new tables when all they needed was a new field.

    • Michael_Cornelissen
    • 3 mths ago
    • Reported - view

      and

    Thanks guys,

    I think I know where to go from here. I'm ready for an elegant solution with enough tables, but not too many to make it work 😉.

    Thanks again.

    • Fred
    • 3 mths ago
    • Reported - view

    Here is something I made awhile back that show what could be done for your work orders as product/supply mgmt. It is made for 1 level between supplies and product. If you have half products that go into finished then that is another redesign that I'm currently figuring out.

    Production.ninox
      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      Great!! Looks like I could use this with a couple of tweeks. Thanks a lot.

      Mike

      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      Hi Fred, I do have a lot of questions now. Is there anyway I can get in touch with you?

      Thanks,
      Mike

      • John_Halls
      • 3 mths ago
      • Reported - view

       Hi Fred. I'd have a single table for Articles and Materials, otherwise you are going to end up with a third for WIP, with a simple BOM between the item being made and the items being used. Then you don't have to concern yourself with levels of build.

      Regards John

      • Alain_Fontaine
      • 3 mths ago
      • Reported - view

       yes, this subject has been discussed several times. See for example:

      https://forum.ninox.com/t/y4hrt5g?r=q6hrnt9

      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      Hi John, thanks for your input. This was where I initially was going with one table Products (Articles) with categories:

      RAW
      Sub-Assemblies
      Finished Products

      S-A and FP will then have a sub-table with the production formula of RAW articles. I was then going to use that when I send an Offer it will give a reserved status on the inventory and when the order is an Order Confirmation it will deduct it from Inventory.

      I like the elegant solution of Fred, but I think I need some help if I go that route. I have very limited experience with Ninox, and almost no experience with scripting.

      The part I am struggling with is that Production (Work-Order) can be made up of different combinations of Finished Products, S-A and RAW or only RAW.

      I hope I have explained it clear enough.

      Again I greatly appreciate all the input and help.

      • Fred
      • 3 mths ago
      • Reported - view

      I've modified the DB to:

      1) added a Yes/No field in Articles called Sub that allows you to specify if it is a subassembly.

      2) I stuck with the material table as I just has trouble not separating the raw materials from the product (or articles). To that end, you can add your subassembly to the materials table, so they can show up as materials for your product. When you link an article it copies the name for you.

      The issue with that is now you have to track inventory of the subassembly in two places. I have fixed the post production button to modify the numbers in both places, but if someone where to manually change things then your numbers would be off.

      One solution, you could create a formula field that is hidden when it is not a subassembly and displayed when it is. Then hide the InStock field to be the opposite. The formula field would then just point to the subassembly Avail field in Article. Then you would need to change the Available formula, but that is easy.

      The part I am struggling with is that Production (Work-Order) can be made up of different combinations of Finished Products, S-A and RAW or only RAW.

      Doesn't your work orders only build products? Each product is made of materials that are linked, so it would list the products then list the materials. Do you often need to add materials that are not part of a product?

      If you need to add materials to invoices then that is another issue and shouldn't be mixed up with your work orders.

      Production2.ninox
      • Michael_Cornelissen
      • 3 mths ago
      • Reported - view

       

      You're the best! Thanks for that. I will add this to my DB. It does make more sense now with a sales side and a production side and you're subassenbly solution will make it easier to work.

      Mike

Login to reply

Content aside

  • 3 mths agoLast active
  • 18Replies
  • 151Views
  • 5 Following