Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Please input on how to structure my database.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please input on how to structure my database.

    Hello, thanks for reading my post. I am new to databases and have been away from working on my first project, and just returned to get back into it. My question is when to break up my database into related tables. It is for a repair business, a brief description would be along the lines of.......
    A main record of the job itself which I would say is where the most information is to be located. I will have department views in alpha.
    The fields I have are ,
    repair order, item, size, color, date recieved, date due, parts required, parts status, parts to be reconditioned, customer rep, assesment technician, assesment status, repair technician, repair status, reassembly technician, reassembly status, verification technician, verification status, also a time in and time out field for each stage of the repair . each department will have its own view in alpha to track the repair, but not necessarily viewing every field that belongs to the item. also I have a parts table listing all the possible parts that may need to be repaired or the parts that may be replaced I do not know if the parts required should go in the main table or get their own table linked to the repair order. and if I should put the status' in their own table or the time ins and time outs in their own table
    Last edited by paintacar; 02-17-2012, 12:18 AM.
    Jerrod Sand

  • #2
    Re: Please input on how to structure my database.

    You should do a Google search on database normalization. Don't get too caught up in the various rules of normalization, but rather find some simple examples showing how tables are broken down to their most basic components. Then you can think of your situation and your data and apply that. But generally speaking in a business like your you may have tables such as:

    customers
    technicians or employees
    departments
    orders
    order line items
    inventory
    etc, etc

    But do the Google search. A couple of hours of reading may go a long way in getting your head around this.
    Peter
    AlphaBase Solutions, LLC

    Peter@AlphaBaseSolutions.com
    https://www.alphabasesolutions.com


    Comment


    • #3
      Re: Please input on how to structure my database.

      Peter, I have been looking up database design in the Mere Mortals book and The Database Development for Dummies which is simpler and straight foward. ( I know not a for dummies book) but actually it is a great book, both me and my associate have been trying to understand it. I do know what you are saying about normalizing, and the ER model or the Semantic object model. But I am worried about breaking it up into too many small tables because I keep reading that being too normalized can cause severe performance problems when you call the records up in alpha I am worried that it will be too slow retrieving the combined records from several tables.

      I am expecting to be adding about 2000 repairs per year and having about 50 repair orders being accessed by 10-15 different people throughout the day everyday. and 5 different departments each accessing most of the same information but some will only be department specific fields for their department view. One main table was my first plan and using several other tables to supply information such as parts and employees to populate the main table from dropdowns in alpha, this will all be mysql and web based. I undersatnd each record has only one instance of each of these bits of information, but from what I read I should group them as what type of information (I understand that for each table I break off I will be repeating the related fields in each table) even though I can directly relate each instance to only one repair order number this would result in a table with about 80 - 120 fields and many of them not being populated because many times they will all not be required for each repair order, which points to breaking up the table to several, I have no problem doing that.

      Then I started thinking to seperate the time in and out fields and the parts fields for each record, and when I was reading that you could over normalize the table causing retrieval to be very slow I am getting worried about that. Something like this, If lets say I need several parts for a repair order would I be better off putting all 20 different parts field into the main table even if most items may only need one or two parts? or better off to put the parts into a seperate table? and not too sure how best to have the parts set up for alpha I thought something like to have them as a "yes/no" field where the default is "no" and a checkbox to toggle if they are needed, or how would you suggest to have a list of things that may be needed but you do not want to display them in the alpha views if they are not needed, only if they are needed. Or should I perhaps have all the parts listed as fields in a table and null until I populate the field with the name of the part? For some reason I am lost at the moment. Maybe there are just many ways to accomplish this.
      Last edited by paintacar; 02-17-2012, 01:29 AM.
      Jerrod Sand

      Comment


      • #4
        Re: Please input on how to structure my database.

        as someone who is very new to this... I spent WAYYYY too long reading and worrying and thinking.

        Dive in.
        Make a decision and do it.
        If you like what you did, fine.
        If you find it isn't doing what you need, you'll adjust the tables

        That's the value of doing it yourself; you'll be in control.
        Key is to stay agile and go with the flow.

        yes, this is in total contradiction to the waterfall method, of charting everything out and knowing where you are going before you start.
        MSQL since 2010
        A5V11 since Feb 2012

        Comment


        • #5
          Re: Please input on how to structure my database.

          Hi Jerrod,
          2000 repairs per year and having about 50 repair orders being accessed by 10-15 different people throughout the day everyday
          These numbers are a fraction of what Alpha can handle, so there is no need for you to worry about that.

          I would suggest you have a good study of AlphaSports, this sample application gets shipped with Alpha. It does most of what you want.

          You have been given really good advice from Peter whom I regard as an expert and I would also take Marion's suggestion about getting stuck in. With that in mind start with the Customer table, form and report. Have a Main Menu form to place your buttons and enjoy how the application grows and does what you want.
          Last edited by Keith Hubert; 02-17-2012, 03:59 PM.
          Regards
          Keith Hubert
          Alpha Guild Member
          London.
          KHDB Management Systems
          Skype = keith.hubert


          For your day-to-day Needs, you Need an Alpha Database!

          Comment


          • #6
            Re: Please input on how to structure my database.

            Jerrod, suggest you avoid a highly normalised approach. The purists may criticise the design, but then they would have all the time in the world to maintain or change it, wouldn't they!
            In the old days data storage was expensive so there was a good reason to have succinct data sets with minimal redundancy.
            I venture that this is not the case any more, and that ease of development, use and maintenance is more important. A bit of redundant data won't hurt, but not to the extent that the design is a dogs breakfast.

            When you analyse the data you need, it will naturally fall into linking data - that which flows through a system and is the data which joins everything up, and information data.

            Easiest way to do some analysis is to use something like Excel with Columns for the Tables and rows with the data names/attributes so you will see any necessary and unnecessary duplication, or Visual Paridgm Community Edition - more fun but quite complex.
            Ted Giles
            Example Consulting - UK
            .

            sigpichttp://ec12.example-software.com//
            See our site for Alpha Support, Conversion and Upgrade.

            Comment


            • #7
              Re: Please input on how to structure my database.

              Originally posted by paintacar View Post
              Peter, I have been looking up database design in the Mere Mortals book and The Database Development for Dummies which is simpler and straight foward. ( I know not a for dummies book) but actually it is a great book, both me and my associate have been trying to understand it. I do know what you are saying about normalizing, and the ER model or the Semantic object model. ...
              Okay, sounds like you are way ahead of the curve, Jerrod. I think Marion has the right idea. Just dive in and do something. A lot of times experience is the only way to get it "right", meaning whatever you build now you will learn from. The good news is even if you start going down the "wrong" path, you can fix it later w/o too much difficulty. And I agree with Ted & Keith, you don't need to over "normalize". We all use a a little redundancy as suits our needs. Go for it!
              Peter
              AlphaBase Solutions, LLC

              Peter@AlphaBaseSolutions.com
              https://www.alphabasesolutions.com


              Comment


              • #8
                Re: Please input on how to structure my database.

                Thanks guys,
                I have been busy lately and not been able to be working on this, but I did at first start to just do it with a couple of tables I had one main table that stored all the information for each item including the item's details for itself and what technician was assigned to the item for each department and each department's status along with two fields for each department that stored the time they started it and finished it. Then I had one table that stored the current employees names that could be changed by managers from another page and a table of the current product names and current vendors, each of these would be used in a dropdown to populate the main table from a dropdown list. I then was reading and thinking that I should at least maybe move the departments from the main table and give them their own table where the department information along with technicians and status is stored seperate from the items. But I was not sure if they should be seperate tables for each department or if I should put the status in one table and the technicians in another and the times in and out in another. I guess I could really do it any way I wanted, but I was looking for possibly general rules. One so that late if I needed help or had problems somebody would be able to understand it and I wouldn't have performance issues with retrieval times. I don't want to end up with a huge problem of restructuring the whole layout once it starts to build a lot of records. Maybe trying to overthink this, but 5 P's.(proper planning prevents prevents poor performance).
                Jerrod Sand

                Comment


                • #9
                  Re: Please input on how to structure my database.

                  I guess I could really do it any way I wanted, but I was looking for possibly general rules.
                  Jerrod - you remind me of me.

                  Everytime I said this to my SQL mentor his response was the same: Assume that what you're currently building is going to have to be re-built and just embrace that....

                  Sure, there are some general 'right' and 'wrong' about the process, but the questions I was asking were not of that ilk.

                  When I was researching all of this to get started, I learned that there are 2 basic styles of development: Waterfall and Agile. You can read about them at wikipedia.

                  As much as I want to plan and know where I'm going - I even started down that path again when I started to learn A5 - the reality for me is that I have to work in an agile fashion.

                  I think the waterfall method (ie, 5Ps works better when there are whole departments devoted to the development of an application, and you have $$$$$ and lots of time before it's needed.

                  For my situation (and I don't know enough about yours), I'm more of a mom-and-pop shop. It's about making a decision, doing it until it doesn't work, and having a great back-up in place before I overhaul!!!

                  Here's an example of a major change I made to my whole db design - I couldn't decide whether an entity should be limited to 2 phone numbers, or have a separate table for unlimited phone numbers per entity. Each method had merit. I made a decision, and went with the limitation. Two years down the road, it annoyed me too much, so I overhauled and created an additional table. And then I went thru each view and report, re-coding as necessary.

                  All of that being said, I will add:
                  most of my changes have been to create separate tables, to hold dedicated info, rather than to try to squash dissimilar info into a single table. When I was smooshing stuff into fewer tables, I invariably wound up needing extra columns that only pertained to a handful of records.

                  So, my rules of thumb is based on: do I need extra columns for this sub-set of records, that won't pertain to any of the other rows in this table?

                  If the answer is yes, then that sub-set gets its own table.
                  If the answer is no, then the data co-habitats (until I get new info and find I need to break it apart).

                  Maintaining consistency with table and columns names is helpful, i think, for other developers down the road (eg, I use ID as a suffix for a key that is an integer, and CODE as a suffix for a key that is alpha; I use Label as a suffix for the longer name that's 25 characters, and Descr for the longer blurb that's 250....)

                  Lastly.... I resisted this for YEARS, but now I totally get it: use the Northwind sample database to learn from. In the past, I couldn't 'see' the resemblance between NW and my data and that was an obstacle for me. But now, I'm able to see past that. While my business does not deal with Orders and OrderDetails, that is simply a standard-one-to-many relationship. And, when I'm trying to see how to do something with A5, I can 'try it out' with NW data.


                  meh - that was a lot. Not sure any of it's helpful. I'm avoiding doing what I'm supposed to be doing...
                  MSQL since 2010
                  A5V11 since Feb 2012

                  Comment


                  • #10
                    Re: Please input on how to structure my database.

                    Hi Jerrod,
                    How is business being done now? It is computerized or manual? Have you seen any software currently used by others in this business or one that is similar? Cause a screenshot or a manual form is a good place to begin to figure out how to break out the data that is static from that which will be growing dynamically.

                    A workorder for instance would be using lookup lists to select a customer, an employee and a dept which is either linked to the employee or separately chosen. Either way a table for using the fields related to departments would be useful if you want to look this data up in several other tables. If depts are only related to employees then creating a static lookup list in the field rules for the employee table could be done without the need for a separate table. However if you have employees that are not tied to just one dept, then a dept table makes more sense.
                    Robin

                    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                    Comment


                    • #11
                      Re: Please input on how to structure my database.

                      Marion, if you are using Agile (as I do when protoptying) do you also use a Scrum approach to the development elements?
                      Ted Giles
                      Example Consulting - UK
                      .

                      sigpichttp://ec12.example-software.com//
                      See our site for Alpha Support, Conversion and Upgrade.

                      Comment


                      • #12
                        Re: Please input on how to structure my database.

                        Ted, yes, I do use a Scrum approach to the extent that:

                        A key principle of Scrum is its recognition that during a project the customers can change their minds about what they want and need (often called requirements churn), and that unpredicted challenges cannot be easily addressed in a traditional predictive or planned manner. As such, Scrum adopts an empirical approach—accepting that the problem cannot be fully understood or defined, focusing instead on maximizing the group’s ability to deliver quickly and respond to emerging requirements
                        I wear every hat related to my project, including owner, developer and customer. The goal is to keep learning without spending all of my retirement savings, automate my work, possibly be able to market the app or take on additional work if necessary to continue to support my family, continue to supply reports in a timely fashion during the transition from old spreadsheets to new database.

                        I am not saying it is not important to plan; I have 100s of pages of planning.

                        But - the biggest things I learned along the way was to not think in terms of "is this the right way to do it" and to embrace re-structuring.
                        MSQL since 2010
                        A5V11 since Feb 2012

                        Comment


                        • #13
                          Re: Please input on how to structure my database.

                          Then you will be a "natural" at prototyping.
                          Never met a customer who - once involved in prototyping - didn't realise the issues involved in changes.
                          I liken it to an appendectomy. You take it out but need to make sure the bits are joined up when you close

                          When the "ToDo" list exceeds 2 pages, tear it up. It will never get done.
                          Feel the freedom! 48 tasks (A4 ruled) out the window.
                          Last edited by Ted Giles; 03-11-2012, 02:27 PM.
                          Ted Giles
                          Example Consulting - UK
                          .

                          sigpichttp://ec12.example-software.com//
                          See our site for Alpha Support, Conversion and Upgrade.

                          Comment


                          • #14
                            Suggestions on how to map your fields to supporting tables

                            You said: A main record of the job itself which I would say is where the most information is to be located.
                            This will be your invoice file.
                            You said: I will have department views in alpha.
                            Assign each employee to a department
                            You said: The fields I have are ,
                            date recieved, date due
                            repair order,customer rep,
                            Make these part of your invoice file
                            You said item, size, color, , parts required, parts status, parts to be reconditioned
                            You will need a parts file
                            You said:assesment technician, assesment status, repair technician, repair status, reassembly technician, reassembly status, verification technician, verification status,
                            You will need a labor operations file: description,department
                            You said also a time in and time out field for each stage of the repair .
                            You will need a labor employee file: employee,department
                            You will need a time card file: employee, labor operation,operationstarttime,operationendtime
                            each department will have its own view in alpha to track the repair, but not necessarily viewing every field that belongs to the item. also I have a parts table listing all the possible parts that may need to be repaired or the parts that may be replaced I do not know if the parts required should go in the main table or get their own table linked to the repair order. and if I should put the status' in their own table or the time ins and time outs in their own table[/QUOTE]

                            Comment

                            Working...
                            X