Which relationships to use in my design?

[mbenton]

I have attached my database. I need help establishing relationships given my objectives. I'm confused because I don't know where the relationships need to be on the table level. My goal here is to receive help in building good table design so that I can continue building my queries and forms.

Background: Our company manufactures auto parts. Each part has a part number, batch number, supplier code, and even a shipping dock code which is a combination of a letter and a number i.e., N1, S2, they are the names of our docks. All numbers assigned to parts such as the part number and Kanban are static with the exception of the shipping dock code, it changes for many parts every period, sometimes 2 or 3 times potentially. There are a series of tasks that needs to be performed, by each of 5 departments, related to the parts before the dock code change begins. The purpose of this database is to manage that dock code change/task related-workflow process.

WorkFlow: Parts that were shipped from a particular dock, i.e., N1 dock for one period may be shipped from S2 dock in another period. We call this processes Dock Code Change. I import the Dock Code Change information from an excel spreadsheet. The data goes to my Import table (Importtble) in my database which then goes to my Partstbl. Task assignments with due dates are housed in my Tasktble. The goal is to assign tasks to 5 departments. One department is assigned to complete task 1, 2 and 3, while another department will complete task 4 & 5 etc...This is routine and does not change. In other words those same departments will complete those same series of tasks each time there is a dock code change. In fact, the only thing that changes in this process are the due dates, a different group of parts, and the dock codes. Each task has a due date. My task and due dates fields are located in my Tasktbl. I want to be able to select a period via drop down combo and it bring up all parts and tasks for that period. Also I want to have the ability through the database to select any given Department (there are 5 departments listed in my Departmentstbl), and have the database filter by department and period all related tasks completed by that department. Also, users should only see current period data on the task form. For example, if we are in period 10 I only want the parts received in period 10 to show on my form.


Table Descriptions:
Headertbl - holds information such as current date and Dock Code change date, identifies which line parts are from (there are only two options here).
Partstbl - Information on parts, this is an input table...all this information comes to me through an import file.
Rejecttbl - This is a "pre" task process where all parts are examined and given approval. If parts are rejected they go into this table.
Tasktbl - stores all tasks and due dates (stores what needs to happen, when, and by which department) *most important table and is probably the heart of the database.
Usertbl - Connected to header...holds the names of those who has the ability to submit Dock Code Change requests.


DockChgtbl - Table that keeps info on when Period starts/ends and dock code change date.

Given that background, I'm unsure about my relationships. I want to make sure my design will accomplish my objectives. In summary, the database table relationships should support:

1. Forms should only show current period data.
2. Filter by Department and Period.
3. Assign various tasks to all parts.

Dock Code Changedb.zipDock Code Changedb.zip

[ranman256]

I rarely use them throughout the database. I ONLY relate PARENT/CHILDs. You dont want to loose parent records and create orphans, so..
I always set parent/child tables with NO CASCADE DELETE. This prevents any deletion of parents if they have children recs.
You dont need them to fill in say, states from the tState table. You dont need this relationship on a lookup table.

tOrder --> tOrderItems
THIS is a relation you want to protect.

[mbenton]

I'm not understanding your reply. How do you identify a "Parent/Child" relationship? Most likely the table relationships in this database as well as the majority of the ones I've seen are either "One to Many", or "One to One" Are you suggesting that I can probably carry out the objectives I mentioned through the use of Queries and Forms? Are there any other table level relationships you think I'll need in my design?

[mbenton]

I know I'll need to create a form that displays fields from Partstbl and Tasktbl. Wouldn't I need to create a relationship between those two tables before creating my form?

[orange]

Do you have a clear description of what you are trying to do? It seems to me that you have not gotten your tables designed, have no relationships and are attempting to build forms.
Relationships are not arbitrary things. Relationships are derived from your Business rules/facts.

You might want to spend ~45 min working through this tutorial. or this one

[mbenton]

Thanks,.. Yes, I have a clear description of what I'm trying to do. I want users to see parts and task/due dates on a form with the ability to search by period and further filtered by department. With that end in mind I'm backing up at the beginning table level to create the needed relationships that will yield those desired results. I only speak of forms because this is not my first attempt at design this database and I've gotten as far as developing forms and queries. Nevertheless I'm back to the basics because I'm missing something in the rudiments of the design and I think it's not understanding how I should form the relationships and which tables should participate in the relationships. As you can see in my database I only have the tables and not forms nor queries because table relationships are my primary focus.

[orange]

Did you work through one of the tutorials I mentioned?
That will guide you to the relationships.
Read through this dialog/exchange and draft model and see if it helps with relationships and business facts.

[mbenton]

I've just worked through them and it was familiar as those are the steps I follow when designing. Also, I have the book you mentioned and read it through a couple of years ago. I'll revisit the book. The area where I'm stuck is do I go through the exercise of comparing each table with every other table to see if a relationship exist? Are there certain types of tables that I shouldn't include in a relationship with other types of tables. For instance, one table may hold slow moving data which rarely changes while other tables hold other types of data that changes frequently. I will create some specific examples of what I'm trying to explain, thanks again.

[orange]

Take a look at post 20 and 21 in the link I gave you.

[mbenton]

Thanks, I read posts 20 and21. I'm not sure what they want their database to accomplish but it sounds like it's on a much large scale than what I'm attempting to do with my database, however, the industry is similar, they build trailers, we build automobiles. I'm trying to track a group of parts through a series of tasks. I'll create the relationships based on my understanding of the concept and let you know how/where I'm experiencing my problem.

[orange]

I'm not sure what they want their database to accomplish

Did you read any of the dialog?

...it will then be scheduled for delivery (or collection) and we require the dealers to fill out a PDI/Registration form so we have a record of the customer who has purchased it. This makes sure that the trailer meets the order and customer satisfaction. It also will help me in keeping track of warranty claims/service visits as sometimes things do go wrong.....

The key to database is to get the tables designed and the relationships set up based on the facts of the business. The database is intended to support the business, so ensuring the database structure reflects the business is critical.

[ssanfu]

The first thing I would do in your dB would be to get rid of the look up FIELDS........

See
http://access.mvps.org/access/lookupfields.htm and
http://access.mvps.org/access/tencommandments.htm