Relationships... who needs em anyways?

[blyxx86]

I am having the darndest time trying to get this relationship working easily.

The areas where I'm having the hardest time of relating information is where there are multiple branches relating one table to multiple tables.

You see where ModelNumber in the tblModels table connects to ModelNumber in tblMainA, tblOutDetail, tblInbDetail...

Well if I were to go into that table and click the little plus arrow next to the record, it asks me to choose a relationship. Then I can't choose another after I close the table. How do I modify that?

Also, if you see any issues within the relationship, please let me know. I'm open for suggestions.

See relationship chart here

[MMcCarthy]

tblMainA Stores the Model Number so you don't need to store it in tblInbDetail and tblOutDetail. This is duplication as tblMainA has an indirect relationhip to these tables.

Mary

[blyxx86]

I figured out how to modify the "Subdatasheet" of a table.

Also..

The suggestion you made. I believe it would limit my users to select only the model suggested in tblMainA?

I think it's doing that now anyways... I guess what I need to do is be able to put different ModelNumber's in each of the 'tblInbDetail' and 'tblOutDetail' ... is that just me using a lookup field instead of a relationship?

Multilevel relationships are not something I am used to working with, so this is all foreign to me.

My goal is this.

tblMainA contains a 'Requested Model'
tblOutDetail and tblInbDetail contain the actual 'ModelNumber' that was shipped/received.

How would I then relate the tables so that it is accurate. Requested model, shipped model, received model.

I don't know if I am making sense, but if you need more information regarding this, please let me know.

[NeoPa]

From simple observation I would advise merging the two pairs of tables :
tblInb with tblOut and tblInbDetail with tblOutDetail.
A flag field may be added to separate the 'In's from the 'Out's in both cases.
Have a look through (Normalisation and Table structures) for a fuller explanation.

[MMcCarthy]

I figured out how to modify the "Subdatasheet" of a table.

Also..

The suggestion you made. I believe it would limit my users to select only the model suggested in tblMainA?

I think it's doing that now anyways... I guess what I need to do is be able to put different ModelNumber's in each of the 'tblInbDetail' and 'tblOutDetail' ... is that just me using a lookup field instead of a relationship?

Yes

Multilevel relationships are not something I am used to working with, so this is all foreign to me.

My goal is this.

tblMainA contains a 'Requested Model'
tblOutDetail and tblInbDetail contain the actual 'ModelNumber' that was shipped/received.

How would I then relate the tables so that it is accurate. Requested model, shipped model, received model.

I don't know if I am making sense, but if you need more information regarding this, please let me know.

Firstly rename the ModelNumber fields to indicate the differences between them and as you said earlier this is just a table lookup rather than what is considered a relationship. What are you trying to do that is not working. I get the impression from a previous post that you are trying to add a second relationship between two tables, why and where?

Mary

[blyxx86]

Yes



Firstly rename the ModelNumber fields to indicate the differences between them and as you said earlier this is just a table lookup rather than what is considered a relationship. What are you trying to do that is not working. I get the impression from a previous post that you are trying to add a second relationship between two tables, why and where?

Mary

I will make some modifications to the relationships today and give it another go.

I really do appreciate your help on this. I have a feeling it's going to get more complicated over the next couple days in terms of the queries I'm going to need to create, but I think I will be able to manage those and any aggregate functions that are included within. I've just been having troubles setting up relationships. This is my first database with somewhat complex relationships. My other databases have contained only a couple one-to-many relationships. So this is all a leap forward.

One question though. When I use the lookup wizard, it creates a relationship for me. I know it doesn't require the relationship, but will deleting that make anything bad happen down the line?

[MMcCarthy]

I will make some modifications to the relationships today and give it another go.

I really do appreciate your help on this. I have a feeling it's going to get more complicated over the next couple days in terms of the queries I'm going to need to create, but I think I will be able to manage those and any aggregate functions that are included within. I've just been having troubles setting up relationships. This is my first database with somewhat complex relationships. My other databases have contained only a couple one-to-many relationships. So this is all a leap forward.

One question though. When I use the lookup wizard, it creates a relationship for me. I know it doesn't require the relationship, but will deleting that make anything bad happen down the line?

The wizard creates the relationship and this is correct so don't delete it. You just have to think of it differently. Lookup tables are not normally included in queries unless associated information is required.

Remember you don't need to have a relationship between table A and C if there is already a relationship between tables A and B and tables B and C as you can use these relationships in any query on tables A and C.

Mary

[blyxx86]

The wizard creates the relationship and this is correct so don't delete it. You just have to think of it differently. Lookup tables are not normally included in queries unless associated information is required.

Remember you don't need to have a relationship between table A and C if there is already a relationship between tables A and B and tables B and C as you can use these relationships in any query on tables A and C.

Mary

Then what's the purpose of the relationship if it doesn't really do anything?

Does the lookup query require the relationship to function correctly since it is using a SQL statement to get the information for the Drop down.

I will have to take a breather and try to reorganize my thoughts and questions on the matter. Because I require that the ModelNumber in the far right tables be influenced by 'referential integrity cascades.' So the requirement for them to be related via a one-to-many relationship is still required... I think?

[MMcCarthy]

Then what's the purpose of the relationship if it doesn't really do anything?

Does the lookup query require the relationship to function correctly since it is using a SQL statement to get the information for the Drop down.

I will have to take a breather and try to reorganize my thoughts and questions on the matter. Because I require that the ModelNumber in the far right tables be influenced by 'referential integrity cascades.' So the requirement for them to be related via a one-to-many relationship is still required... I think?

So if you delete a model number you want every record referencing that model number to be deleted?

Mary

[blyxx86]

So if you delete a model number you want every record referencing that model number to be deleted?

Mary

Well, what I want to do is ... probably not very complicated.

We have the ReqModelNumber in a one-to-many relationship.

There is no relationship binding 'ModelNumber' in the table "tblOutDetail" but if the ModelNumber changes in the original 'tblModels' table then that update will not cascade to the 'tblOutDetail'

The database is still in design mode, and I'm trying to think of any possible future proof things I can before I release it to the hounds (aka coworkers).

[MMcCarthy]

We have the ReqModelNumber in a one-to-many relationship.

There is no relationship binding 'ModelNumber' in the table "tblOutDetail" but if the ModelNumber changes in the original 'tblModels' table then that update will not cascade to the 'tblOutDetail'

The database is still in design mode, and I'm trying to think of any possible future proof things I can before I release it to the hounds (aka coworkers).

Using the model number as the primary key is going to be a problem if you are going to ever invision changing the model number.

Rule#1:
THE VALUE OF A PRIMARY KEY INVOLVED IN A RELATIONSHIP SHOULD NEVER CHANGE.

Having got that out of the way create a new autonumber primary key and take the primary key off model number. Now create a new foreign key field in tblMainA to reference this new ID and update to the ID where modelNumber in lookup table is the same as the old primary key. You can now delete the old foreign key reference to modelNumber and if you change the model number in the tblModels it won't change the primary key so the reference remains the same. Do the same for any other tables with a relationship to tblModels.

Mary

[blyxx86]

Using the model number as the primary key is going to be a problem if you are going to ever invision changing the model number.

Rule#1:
THE VALUE OF A PRIMARY KEY INVOLVED IN A RELATIONSHIP SHOULD NEVER CHANGE.

Having got that out of the way create a new autonumber primary key and take the primary key off model number. Now create a new foreign key field in tblMainA to reference this new ID and update to the ID where modelNumber in lookup table is the same as the old primary key. You can now delete the old foreign key reference to modelNumber and if you change the model number in the tblModels it won't change the primary key so the reference remains the same. Do the same for any other tables with a relationship to tblModels.

Mary

I was a step ahead of this before I got to reading it. I understand the rule, but then what is the purpose of the 'cascade updates' if a primary key is not supposed to change? cascade delete, i can see the purpose in any case (although kinda scary at the same time)

the 'modelnumber' shouldn't ever change and i would like to leave it as a primary key, but at the same time I can understand that setting up an autonumber field would work too.

I've since modified the relationship again, but what conceptual flaws do you see with the current design. With lookups for the two tables (tblInbDetail / tblOutDetail) ... or do you feel that the relationships have been, mostly, set up correctly now?

[NeoPa]

Rule#1:
THE VALUE OF A PRIMARY KEY INVOLVED IN A RELATIONSHIP SHOULD NEVER CHANGE.

I was a step ahead of this before I got to reading it. I understand the rule, but then what is the purpose of the 'cascade updates' if a primary key is not supposed to change?

The rule that Mary is quoting is a recommendation of how things should work.
Access will not constrain you to doing things in the recommended way. If you choose to change the PK then Access can handle making sure all related FKs are changed to match.

[blyxx86]

The rule that Mary is quoting is a recommendation of how things should work.
Access will not constrain you to doing things in the recommended way. If you choose to change the PK then Access can handle making sure all related FKs are changed to match.

NeoPa,

In regards to your earlier suggestion about merging the two tables together (Inb and Out) my only concern there is that there are differences between the two (additional fields I have only recently added like 'Date') which can vary between inbound and outbound.

I can see how combining the two 'detail' tables could work out to be beneficial however. I can possibly think of a way to merge the 'Inb' and 'Out' tables, but I'm worried that the queries relating to that table would become more complex by being combined.

One conceptual question for you.. Is there a rule that would state a PK in one table should not be a FK in multiple tables? Like in my example of the 'Incident' being bound to the 'tblInb' and 'tblOut' tables?

[NeoPa]

In regards to your earlier suggestion about merging the two tables together (Inb and Out) my only concern there is that there are differences between the two (additional fields I have only recently added like 'Date') which can vary between inbound and outbound.
I can see how combining the two 'detail' tables could work out to be beneficial however.

Unless there are a large number of such fields I would just add the field anyway and leave it set to Null (unused) for those records (or the types) that don't need them.

I can possibly think of a way to merge the 'Inb' and 'Out' tables, but I'm worried that the queries relating to that table would become more complex by being combined.

I can see you're getting to grips with some of the database concepts that make life so much easier once understood (We'll make an expert of you yet :)). Basically it's a different way of thinking about databases and how they work. For me now, I actually find dealing with a single table but with JOINs or a WHERE clause to specify a filter of which records are selected, easier than working with multiple tables whose relationships and MetaData need to be bourne in mind when working with them. I would recommend storing the similar data together in one table.

One conceptual question for you.. Is there a rule that would state a PK in one table should not be a FK in multiple tables? Like in my example of the 'Incident' being bound to the 'tblInb' and 'tblOut' tables?

I don't think so (You could check (Normalisation and Table structures) but I don't remember seeing it).

[MMcCarthy]

One conceptual question for you.. Is there a rule that would state a PK in one table should not be a FK in multiple tables? Like in my example of the 'Incident' being bound to the 'tblInb' and 'tblOut' tables?

No there isn't. Think of this example.

tblCustomers
CustID (Primary Key)
CustName

tblOrders
OrderID (Primary Key)
Product
CustID (Foreign Key referencing Primary key of Customer table)

tblInvoices
InvNumber (Primary Key)
InvDate
InvAmount
CustID (Foreign Key referencing Primary key of Customer table)

In this case CustID the primary key of the tblCustomers table is referenced by both the tblOrders and the tblInvoices table. However, there in no relationship between tblOrders and tblInvoices. This is the correct and classic structure for this kind of database.

Mary

[blyxx86]

No there isn't. Think of this example.

tblCustomers
CustID (Primary Key)
CustName

tblOrders
OrderID (Primary Key)
Product
CustID (Foreign Key referencing Primary key of Customer table)

tblInvoices
InvNumber (Primary Key)
InvDate
InvAmount
CustID (Foreign Key referencing Primary key of Customer table)

In this case CustID the primary key of the tblCustomers table is referenced by both the tblOrders and the tblInvoices table. However, there in no relationship between tblOrders and tblInvoices. This is the correct and classic structure for this kind of database.

Mary

Yet... there is a relationship between them, although not directly? They are both tied to the Customer table.

I modeled my current design after something similar to that example. Because an "Incident" is like a customer, then the 'outbound' is like the order and then the 'detail' is the contents of the order.

There are going to be a great number of fields and records for both tblInb and tblOut so while the data is similar, It is also opposite of one another (Inbound versus Outbound)

I wouldn't mind using a single table, but I doubt I'll be able to, so I'll just have to design a query that will gather the pertinent information to have it display as a table for me.

I don't want to even think of the forms I need to create for my co-workers. Although anything would be an improvement over their current record keeping of excel spreadsheets. Try finding a specific 'Incident' in 2 years worth of daily spreadsheets. NO THANK YOU.

I hope that once everyone here helps me become an expert (although you have called me that once before NeoPa) and I begin designing databases for my work that I will get a pay increase. Do you by chance know the average salary for a DBA? I just recently turned 20 years old so I've got a lot of school ahead of me, and I am currently enrolled in two classes on database design because I find it extremely interesting. However, the first 8 weeks have been basic basic functions like using a static select query to find "john smith" in a table.

[blyxx86]

Check out the new relationships I've created:
New Relationships Layout

Take a look at that new table layout and let me know what you think. I try to follow the Normalization you've suggested and I FEEL that it's in 3rd at least. However, I'd like to know what you think.

I know I need to make a couple changes in terms of PK and allowing some units to be shipped out and returned without an "Incident" number. So I'm going to have to ponder on that for a little while longer.

I'd like to just know where I didn't do the relationships properly.

I decided to keep the 'Out' and 'Inb' separate because they are related in terms of being opposites, although with similar data.. so I'm still a little confused as to what I should do.

[MMcCarthy]

1. You are relating tblPrimary to tblAnalyst and tblPrimary to tblCustomer and tblAnalyst to tblCustomer. This is one relationship too many.

If tblPrimary has an Analyst who is then related to a Customer then remove customer from tblPrimary

OR

If tblPrimary has a custome who is related to an Analyst then remove Analyst from tblPrimary.

2. For the same reasons why is customer in tblModels

3. I understand from previous conversations that ModelNumber is essentially a lookup table.

If tblPrimary references a Customer then all other tables referenced directly or indirectly by that table will have an indirect reference to the customer and don't need to store it.

Mary

[blyxx86]

1. You are relating tblPrimary to tblAnalyst and tblPrimary to tblCustomer and tblAnalyst to tblCustomer. This is one relationship too many.

If tblPrimary has an Analyst who is then related to a Customer then remove customer from tblPrimary

OR

If tblPrimary has a custome who is related to an Analyst then remove Analyst from tblPrimary.

2. For the same reasons why is customer in tblModels

3. I understand from previous conversations that ModelNumber is essentially a lookup table.

If tblPrimary references a Customer then all other tables referenced directly or indirectly by that table will have an indirect reference to the customer and don't need to store it.

Mary

I guess the reasoning behind having the customer in so many locations is that "tblAnalyst" stores information that is specific to each customer, then also some models are specific to each customer as well.

Each analyst is related to a customer, and each analyst is also related to many incidents.

Many incidents are related to one customer.

I could delete the relationship between 'tblCustomer' and 'tblModels' at least for now.

Other than that, is there any other problems you can see? Is it too complicated with all the little guys flying all over the place?

[MMcCarthy]

If there is one Analyst record per customer then you don't need analyst in the incident table as it can be referenced through the customer table.

Your problem is you have too many circular references. This is going to cause you endless problems.

Mary

[blyxx86]

If there is one Analyst record per customer then you don't need analyst in the incident table as it can be referenced through the customer table.

Your problem is you have too many circular references. This is going to cause you endless problems.

Mary

How would you fix that problem?

There are many analysts per customer, but only one analyst per incident.

There are still technically lookup values in the 'Primary' table to the model, but they do not have relationships anymore. Will that cause any type of problem?

[MMcCarthy]

OK

An Incident which is recorded in tblPrimary has one and only one analyst and one and only one customer. Each Analyst handles many incidents and each customer can have many incidents. Each Analyst can work for many customers.

Big question ...Can each Customer have more than one analyst. That is does customer A only ever work with analyst B. What is the full relationship between customers and analysts.

Mary