Results 1 to 7 of 7
  1. #1
    Shelley is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2016
    Location
    Massachusetts
    Posts
    9

    Table Design will be changing daily - Don't want to add Keys yet

    Hi,



    I am a temporary employee that has been hired to create a database in Access to produce every departments daily paperwork and store its information to later produce reports, queries, and pivot tables from.

    The biggest problem I have encountered is that I absolutely know nothing about these departments or even the end product.

    Therefore, I have created many Tables to represent each Work Center, but then I must meet with Supervisors to determine what must go and what must stay in each Work Center (Table).

    With things constantly being added and deleted, it absolutely made no sense to have a Primary Key yet. I initially started with one and then a Supervisor said to delete a Field and then changed his mind and wanted me to re-add it. This immediately became a problem since it would not allow for any duplicates!

    Hence, my decision to avoid any Keys at all right now. I will add them later when the data is all correctly entered.

    Is this unusual?

    My next dilemma is that I saw a tutorial online (the first one I watched of course) which suggested using Source Tables and labeling them ST at the end of each title. It also suggested not to give it a Key at all. This Table was strictly to serve as a Drop Down List. My boss has already used Access before and really liked this idea over using Value Lists.

    For example:

    Source Table: Material: Stainless Steel, Galvanized Steel, Copper, Aluminum, etc.

    Source Table: LouverPartNumber: JVK-S8, JVK-S11, JVK-S14, JVA-S8, JVA-S11, JVA-S14, JVB-S8, JVB-S11, JVB-S14, etc.

    (This Source Table should probably be a regular Table then inserted into the Work Center Table as a Combo Box Field. I will really have to rethink this ST.)

    Anyhow, you get the gist of things.

    If this is the only thing these Source Tables will be used for; then do I really need to give them a Key when it's all said and done?

    Next, I will be creating Forms for the Supervisors to enter the stats for the day for each employee into the database. This data will then in turn update the Records in the Tables.

    I really hope this is the ideal way to be creating this database.

    Please feel free to provide me with feedback.

    Shelley

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Purpose of the database:
    to produce every departments daily paperwork and store its information to later produce reports, queries, and pivot tables from.
    What exactly do these departments do? Plain English, no jargon.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I really hope this is the ideal way to be creating this database.
    Far from it. You should be developing a scope of work on paper after identifying what are the inputs and desired outputs for the processes involved and get agreement on that. If the employer knows what these are, great. If not, you use your db design knowledge to flush them out. These discussions should indicate to you what information (note I didn't say data) is required to support what's desired. Only then do you consider what tables and fields are required, what their data types should be and how to normalize the data as much as possible/practical. That you are altering tables in such a fashion leads me to think that neither you or the client understand where this should be going. I would not involve them in the design beyond a scenario where I might explain the benefits of one method over another if the choice added considerable design time.

    Not until then would I create any tables and their relationships. Looking at the relationship diagram, I would test the design in my mind with scenarios based on the process that I had better understand by now. This would take the form of, if A happens then B but not C can I get D out of this? Can I get this, that and the other? Rather simplistic description, but hope you get the idea. You should also vet the relationships by checking them against any desired outputs such as, can I produce the reports they asked for? Only then would I dump in or create test data and build queries (not forms or reports) and attempt to assemble the data for the requirements that have been identified. If you cannot, there is something wrong with, or something missing from your table design. I would not create forms or reports until the queries were giving me what I need.

    I have also created relationships on large sheets of paper when it seemed easier to erase connections or add/move fields around for large or complicated projects.
    For table design, I found Excel useful for laying out table fields in rows and properties in columns, since there are so many property possibilities. It helps to coordinate a field data type and any limitations (length, allow Nulls, indexed, etc.) between the parent and any related tables.
    Hope some of that helps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to what Micron has advised, I suggest you watch this free tutorial from BA_Experts
    re Business Analysis.
    It will help you identify where to start, the steps involved and why.
    This is what should happen before any database development.
    This video, and others by BA_Experts (Tom Hathaway), are great for concepts and approach and include a little humor.

    Good luck.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    My next dilemma is that I saw a tutorial online (the first one I watched of course) which suggested using Source Tables and labeling them ST at the end of each title. It also suggested not to give it a Key at all. This Table was strictly to serve as a Drop Down List. My boss has already used Access before and really liked this idea over using Value Lists.

    For example:

    Source Table: Material: Stainless Steel, Galvanized Steel, Copper, Aluminum, etc.
    Nothing wrong with using (single field) source tables in principle - usually called lookups or lists. but do make the field a primary key to avoid duplicates. Without knowing more, I would suggest your materials would be a good candidate but not sure about part numbers - it really depends on how the data is going to be used.

    Sounds like nobody quite knows what they want - but they'll know it when they see it. I've had to develop db's this way in the past and it is by far the most inefficient way of doing it. You would be much better advised to draw up a brief of what the db is required to do, where it's data comes from, how it is added to the database and how it is to be reported (i.e. what outputs are required, including any calculations). Also include also some detail of the process required (this can't happen before that, this event triggers that event, etc). Once you have that information you can then sketch out on a pieces of paper what the various forms will look like and get some agreement as to their look and feel.

    Only then are you in a position to design the tables using the principles of normalisation - basically 1) data is stored only once, 2) calculations are not stored and 3) data is not built into the table structure (e.g. week1, week2... or materialA, materialB... field names). Your source tables breaks principle 1, but (in my opinion) is worthwhile for simplicity if that is the case.

    For designing your tables and relationships, I sometimes find using the following layout which works quite well to get the initial draft

    In excel, list all the data in column A. First row always contains a primary key. Then in the other columns start naming tables. The rule is, data is either Stored (S) or displayed (D), but can only be stored once - so you need a table where this can be the case. Data can be display more than once of course. This small example demonstrates.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	15 
Size:	51.2 KB 
ID:	26180
    If data is Displayed, then what needs to be stored in that table is the Primary Key (called a Foreign Key) of the table where that data is stored. Note that the two values highlighted in yellow are another example where the first normalisation rule is broken. In this case because although you may want to lookup the price initially, you then want to store it in your invoice because further down the line, if the price changes in the material table, you don't want it to change in the invoice table (which it would do if displayed). Otherwise if you find you are starting to store data more than once, it is usually time for another table

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you have jumped into Access and started designing tables without understanding what is needed/required.
    It would have been better if you had designed the tables using pencil & paper first.


    I stole the following from a post by orange - it is better than anything I could write: (thanks orange )
    ---
    "I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
    <snip>

    <snip>
    I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

    You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
    ---


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.

    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This is solved????
    ??? What was/is the solution???
    Others may have similar question, so posting your solution could benefit others.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replication Changing Table Design
    By dunc723 in forum Access
    Replies: 3
    Last Post: 12-12-2017, 08:03 PM
  2. I think I put the keys backwards in my database design.
    By Melaniecarr23 in forum Database Design
    Replies: 1
    Last Post: 03-11-2014, 09:15 PM
  3. Database Design: Normalize daily scheduled time
    By Lorlai in forum Database Design
    Replies: 1
    Last Post: 03-12-2013, 12:26 PM
  4. Changing primary keys
    By ksammie01 in forum Database Design
    Replies: 4
    Last Post: 02-07-2013, 07:56 PM
  5. Replies: 2
    Last Post: 05-22-2012, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums