9 Replies Latest reply on Jul 20, 2012 12:20 PM by AntonChuykin

    I need your strategic advise about DB architecture, please

    AntonChuykin

      Title

      I need your strategic advise about DB architecture, please

      Post

      Dear Friends,

      Could you please give me your advise. My DB is built around two main things: orders and invoices. In my orders I use portal row for specification and many-many other add-ons (you can see it on my relationship screenshot). My invoice is 70% of my order with same structure and parametrs. Simple, I want a button MAKE INVOICE on my ORDERS layout and everything will be copied, but I might change my invoice slighlty. First, I made second table for my INVOICES and now I think, that I somehow can use single table for both just with different indexes in key fields like ORD0001 or INV0001. I would use duplitcate (very userful: http://forums.filemaker.com/posts/ac6afe2913), but how can I avoid messing them in one place? Using another layout is one thing, but when user will go thru next/prev records - they will panic.. 

      I'm sure a lot of people here faced the same issue. What is the golden rule for this kind of things? Thank you beforehand.

      Screen_Shot_2012-07-16_at_19.57.39.png

        • 1. Re: I need your strategic advise about DB architecture, please
          philmodjunk

          Using the same table is possible. But instead of putting two different index values in the same field, use two different fields.

          You might also consider the advice in this article as a way to organize your table occurrences so as to make them much easier to work with: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

          • 2. Re: I need your strategic advise about DB architecture, please
            AntonChuykin

            Table occurrences. Yes, that what I was thinking about. But how should I set relations then? 2 times for everything? To make, for example Customers.Order occurrence and Customers.Invoice to make it work properly? Or it is all in that article (thank you for that)

            But, would do you mean two fields? like Serial for Invoices and Serial for Orders with different numeration? If i make a new record in Order occurrence it will effect serial field Invoice, is that true?

            • 3. Re: I need your strategic advise about DB architecture, please
              philmodjunk

              Anchor Buoy does require making additional table occurrences, but you end up with "tree" type stuctures that match the design and function of your layouts and this makes it much easier to work with them.

              I would not put the values ORD0001 or INV0001 in the same field. Especially if this field is a relationship key. Use auto-entered serial number fields to create a primary key separate from these values. I'd use separate fields, leaving the ORD field blank with it's an INV record and vice versa.

              If both Order and Invoice should have the same number, then keep the number portion unchanged and modify a text field to change from ORD to INV as needed.

              • 4. Re: I need your strategic advise about DB architecture, please
                AntonChuykin

                Dear Phil,

                You suggest:

                 I'd use separate fields, leaving the ORD field blank with it's an INV record and vice versa.

                I have only a script trigger in my mind, that will keep ORD field blank, when I make new record. Am I right? 

                • 5. Re: I need your strategic advise about DB architecture, please
                  AntonChuykin

                  Another Q about graphs. I was reading your suggested article carefuly. And i tried to make and order with my graphs. Today they look like my attached screenshot. One thing I mentioned - guy in the article use ID always. I use IDs and their references only in some special occasions, like tables with orders, specification, etc. But i don't use with customers names and smth like that, when I more use names. How critical is it? Should i reconsider it? 

                  • 6. Re: I need your strategic advise about DB architecture, please
                    philmodjunk

                    I have only a script trigger in my mind, that will keep ORD field blank, when I make new record. Am I right?

                    If you mean when you duplicate an existing record, then the script that duplicates the record would also need to clear this field. Don't see where a script trigger would come in to do that.

                    Generally speaking, using names instead of ID numbers is a really bad idea. Names are not unique--you can easily get multiple customers with the same name. Names change when people marry, are adopted or just because they decide they want to change their names. Names are subject to idiosyncratic spelling (Smith could also be spelled as Smythe or Smyth.... and must be either typed in or imported from an external source where they were typed in--which means they could be typed in incorrectly and then you find yourself with an issue where you need to correct the error without breaking the links to any related records. The same issue occurs when someone changes their name. These same issues apply when it's the name of a company instead of a person.

                    All of these issues are avoided if you use an auto-entered serial number where you never permit changes as the primary key in place of a name field.

                    With the right interface design, the user need never enter this ID number into a field, select it from a value list nor even see it on a layout. It's even possible to set up name based dropdown lists that auto-complete on the name, but a script uses the text entered to look up the ID number--with code that handles duplicate names--and enters it into the appropriate ID field to link it to related records.

                    • 7. Re: I need your strategic advise about DB architecture, please
                      AntonChuykin

                      Dear Phil,

                      Thank you for your time explaining IDs. To be honest, I came to this before, i have them nearly everywhere, but I don't use references too often. I'll change it very quick.

                      Could you please help me out a little with table occurrencies. I made new occurrency called Invoices, which is based on my Orders. Also I copied my Orders layout and renamed it for Invoices and made it "show records from Orders".

                      What I have now in my Invoice layout - same 10 (for example) records as in Orders layout. What I want - to make them more isolated. Orders are orders and Invoices are invoices and my users shouldn't ever mess it up. Did i pick proper way to go with my goal? 

                      Why did I ask you about script trigger is because, for example, we have an order with serial 10. I click on a button which will run script to duplicate this order and make Invoice from it. Duplicate will make both serials for INV and ORD to go +1. My order serial will become 11.

                      Next step - i would like to make a new order, so I click NEW RECORD. No, my order serial will become 12. And in my general orders numeration there will be a gap: 10, then 12, because 11 is used for invoice. To fix this, I can use SET SERIAL script step to -1 when i'm making an invoice, but is this the same what you mean or you have another brilliant idea? 

                      Thank you!

                      • 8. Re: I need your strategic advise about DB architecture, please
                        philmodjunk

                        Could you please help me out a little with table occurrencies...

                        If you perform a find on your orders layout, you'll be able to bring up either order, invoice or both records on the same layout. The same is true for performing the same find on an Invoices layout. Specifying different occurrences of the same data source table does not automatically limit the records that may be pulled up in a found set. It does affect how data from related tables--such as those shown in a portal is accessed. The relationship between the layout's table occurrence and that of the related table occurrence will control what appears in that case.

                        What you can do is script performed finds and constrained found sets to limit what type of records are visible on a given layout so that orders are only shown on the orders layout and invoices only shown in the invoices layout even though the records are taken from the same data source table. The main advantage to you for having two layouts that refer to different table occurrences is that each will have independent found sets so performing a find on one layout does not affect what records appear on the other.

                        Here's a script that will automatically constrain the current found set to records of type "order":

                        Enter Find Mode []
                        Set Field [YourTable::orderNumber ; "*" ]
                        Set Error Capture [on]
                        Constrain found Set []

                        You can use the OnModeExit script and OnLayoutEnter script triggers to perform this script to limit the found set to only orders records in most cases. If you have FileMaker advanced, you can also do something about Show All Records and Show Omitted only.

                        Why did I ask you about script trigger is because, for example,...

                        Yes, but we usually don't refer to the click on a button as a "script trigger" though technically, the mouse down event for clicking a button is the original "script trigger" in interface design-- so I was checking to make sure I understood you.

                        or you have another ... idea?

                        First, fileMaker will function just fine if you have gaps in your number sequence. The only reason to avoid them is if the number appears on reports and you need to show that no records were accidently excluded or deleted.

                        Apologies for changing direction on you, but I'd use one auto-entered serial number as the primary key for orders in this table and use separate fields for order and invoice numbers only if they are needed on layouts where people need to see them. In those cases, you can generate serial numbers "on demand" by using a related table set up like this. (I'm using orderNumber here, but another table with the same type of setup can be used for an InvoiceNumber also.):

                        Define a new table and relate it like this:

                        Orders::__pkPrimaryKey = OrderNumbers::_fkOrdersID

                        Enable "allow creation..." for OrderNumbers in this relationship.

                        Define a field, OrderNumber, in OrderNumbers as an auto-entered serial number.

                        When you need to generate an order number, perform this simple script:

                        Set Field [OrderNumbers::_fkOrdersID ; Orders::__pkPrimaryKey ]

                        The first time such a script is performed for the  current record in your parent table, a new record is created in OrderNumbers and and an auto-entered serial number appears in the OrderNumber field. You can put OrderNumbers::OrderNumber on your layout to display this number and you can also use it to perform finds. to find an order of a specific number if you need to. If you mistakenly run this script from the same record a second time, nothing changes as the record already exists and OrderNumbers::_fkOrdersID already has the value that this set field will attempt to store in it.

                         

                        • 9. Re: I need your strategic advise about DB architecture, please
                          AntonChuykin
                          Dear Phil, Thank you for directions!! All clear now! I'm familiar with serial numbers in separate table. You helped me last time as well. Your help is very appreciated!!!