Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: Design theory using normalization

    I am trying to build a database that will allow us to track errors on shipments. I have never built a database using normalization and I'm looking for some direction. I have read several articles about normalization and I'm still not really sure how to accomplish it. I have attached 2 tables with all the data were looking to capture. I could really use a jump start in an effort to learn how truly build a great database.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    It is difficult to make any logical suggestion for you as your fields have no context for me. I have looked at your file and there is no information in design view regarding field descriptions. If you could provide a list of all of your fields and then a description of what the data to be stored in the field does that would be helpful?

    An example...
    StoreNumber - (Text) - the number that identifies the store due to receive the shipment

    Essentially normalisation works on two fundamental principles but this article is useful in understanding the logic behind the principle of normalisation (http://phlonx.com/resources/nf3/):

    1. No repetition of data
    2. Every field in a table is dependent solely on the primary key and nothing else

    The more detail you can offer in your description the better the picture you paint us to help you with a potential solution.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Dec 2010
    Posts
    10
    Dave,

    thanks for the reply. I have added some descriptions and changed the data tables around trying to normalize them. Check out the relationships and let me know what you think..

    What I'm looking to accomplish with this is you enter your store info, you have anywhere from 1 to 5 agents counting cases and units, if an agent finds an error they enter info specific to that casenumber. I need to track errors by casenumber and total errors by store.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are starting out ont he db design world then I'd recommend you look at
    Fundamentals of Relational Database Design -- r937.com
    or
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2012
    Posts
    97
    Going on the DB that you have provided I would change to a five table model and would make some changes to the fields that reside in particular tables as follows:

    tblStores - StoreID, StoreNumber
    tblOrders - OrderID, StoreID, InStoreDate, UnitsAllocated, TotalROI, PercentCounted, Facility
    tblAgents - AgentID, Forename, Surname, Shift, FullName
    tblStoreAudits - CartonID, AgentID, OrderID, UnitCount, CaseCount
    tblErrors - ErrorID, CartonID, SKU, ErrorType, ActionTaken, ErrQuantity, ErrCostPerUnit

    There are a few points:

    1. I would query whether the fields (InStoreDate, UnitsAllocated, TotalROI, PercentCounted, Facility) relate solely to the Store. It would appear to me that these fields are changeable and not fixed information related to the store. This would suggest that a further Orders (tblOrders) may be needed to store information about allocation of units and the rest of the fields relate to audits carried out in store - hence I've moved them so.

    2. In tblErrors it is inefficient to store both the cost per unit as well as the total cost, unless this is a calculated field. If not simply calculate total cost at run time i.e. when displaying a form or report.

    3. If you require tracking of errors by case and by store then you need to have the StoreID in tblErrors. If you use an Orders table the StoreID is stored in the Orders table as an order relates to a store.

    4. I'm not 100% sure on your terms 'boxes, cases and units' and what constitutes each so you may need to change things to suit your needs.

    5. Regards to relationships:

    tblStores (one) - tblOrders (many) - as one store can have many orders.
    tblAgents (one) - tblStoreAudits (many) as one agent can carry out many store audits
    tblOrders (one) - tblStoreAudits (many) as one order can be audited X times by X agents
    tblErrors (many) - tblStoreAudits (one) as one audit may contain many errors

    6. You may want to also allocate agents to particular stores. I wasn't sure whether agents would be allocated so this design would allow for any agent accessing any store. If you wanted to allocate agents to a group of stores you would need to include the field StoreGroup in tblStores and also in tblAgents, this way a particular agent would could be allocated ONLY to one group of stores.

    Forgive me if there are any glaring mistakes, today has been a busy one! Feel free to take this advice with a pinch of salt should you wish; you know your needs better than anyone else and this is simply a suggestion.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  6. #6
    Join Date
    Dec 2010
    Posts
    10
    Dave,
    thanks so much for the info.. been digesting for a few days

    I like what you did with #1 because we could do one store twice in a week, or several times in one month, meaning all the fields in tblOrders would change.

    For #2, the thought was that if you had say 5 errors @ $1 each for one particular CartonID, I would want to know that the total dollar amount for CartonID will be $5, but thinking about it, I really only need the total dollar amount for every error found to be put into TotalROI in tblOrders.

    #3, i will need to track errors by case & store so I added StoreID to tblErrors.

    #5, I tried to setup the relationships, can you check the attached Dbase to make sure it looks ok?

    Just had another thought, I need to have PercentCounted auto update based on UnitsAllocated/UnitCount
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2012
    Posts
    97
    Your welcome.

    Relationships look good to me, only suggestion I would make is that you investigate cascading updates and deletes in your relationships. This is an effective way of maintaining data integrity in the database and is worth investigating and applying where appropriate in line with your needs.

    As for having a field auto update based on entries in another field, this is easily achieved when you build your interface (forms). You would simply run code on the OnChange event of one field that would update the value of another.

    I would suggest that you enter dummy data into your DB straight into the tables to ensure that you are 100% happy with how the relationships work. You need to iron out any errors at this stage and be certain that your DB will work before you go to the trouble of designing any forms/reports etc.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •