Relationships Question

[cwgonnelli]

Hi. I have several tables that have the same field in each of the tables. For example, I have a store number that is present in each of the tables. The store number field is the primary key for each of the tables.

How can I populate the store number in one of the tables and have it feed the other tables?

Thanks for your help!

 

[Niniel]

You establish a relationship between tables via the store number.
But your database sounds a bit odd if you really have this key as a PK in several tables.
What I would expect is to see it as a PK in one table, and maybe a foreign key in others. Then you wouldn't have to do anything about "populating" the tables with that key.

Maybe you ought to post more about your database so that you can improve its design.

 

[cwgonnelli]

Thanks, Niniel. I removed the primary key from the other tables but kept it on the "lead" table. Which now begs the question, how do I create a foreign key?

I tried to keep the database struture simple. For example my lead table is has general information about the store. The other tables that I created include store lease information, store capital expenditure information, store operations information and store strategy information. The primary key is the store number in the general information table. I want to be able to link the store number in the general table to the store number in all the other tables, to the point when I add a new store to the general table, it feeds the store number to all the other tables.

 

[Niniel]

Ok, if the store number is really unique, then I guess you can use that for a PK. Personally, I'm a friend of auto-number PKs, but there are those who don't like that.
Inserting a foreign key into a table is really easy.
Say, your main table PK is called StoreID [your store number]. It ought to be a "number" field.
Then you create a field called "StoreID" in your other tables and set its type also to "number". That's pretty much it.
Now you open the Relationship window, load all your tables into it, and then drag the StoreID PK from the main table to the StoreID fields in the other tables. That'll create one-to-many relationships, ie. each StoreID can be linked to several entries in the other tables.

If that does not describe the relationship between your tables, ie if the information in your tables is applicable to several stores, then you have a one-to-many relationship the other way, and in that case, you need to have a PK in your table/s [as I mentioned earlier, auto-numbers work very well for this], say Table1ID, and create a corresponding Table1ID number field in your main table. Then you drag your Table1ID from Table1 to your main table and establish the relationship.

Another common scenario are many-to-many relationships, but I don't have the time now to go into that, maybe somebody else can do that.
Keep asking question, people here are very helpful.

 

[The_Doc_Man]

CW, what you are describing is a common enough scenario. A main data table and a set of categorized supplemental data tables. I'm going to toss in a bit of theory here. If you knew this, forgive me for assuming you didn't know it, but it is important enough to belabor the issue. I'm doing it because I'm reading between the lines of what you said.

You are looking at a series of tables that depend on the same prime key but that must also have some other identifying information. So, for example, if you have a store in your lead table and you have multiple leases that apply to it (somehow), you would need separate lease identifiers to make the lease entries unique. Whether the lease table's key is only the lease number (and the store ID is strictly a foreign key) or whether the store ID and the lease number are also the table's compound prime key depends on the lease numbering scheme. The same concept applies to your capital expenditures and your other tables. Be VERY careful to watch for your keys here.

What you want to avoid is an unnecessary declaration of table keys because of the rule that you want the smallest possible unique key for any table. So if lease numbers are unique already WITHOUT the store ID, they should not be part of the lease table's prime key. (There is no barrier to them being a foreign key, because that doesn't require an index on the secondary table.) This is a normalization thing. It will save you some trouble down the road.

From your description, I think you are relatively new to DB design. There is often a tendency to overdesign some things - like table keys. To keep your data from being overdetermined, just think about the discussion above and WHY you don't always use Store ID as all or part of a table's primary key.

Don't hesitate to ask questions. Just remember that you are asking of a forum, not a single person, so - to paraphrase Forrest Gump - answers will be like a box of chocolates. You never know what you're going to get.

 

[cwgonnelli]

Thank you for all of your advice. Given the nature of my job, I am now finally able to get back to my database design. As you all can tell, I am new to this. I'm trying to do things the right way, rather than treating Access like a larger version of Excel.

Anyway, I've done what was suggested, but when I populate the information in my lead table, it still doesn't feed those other tables.

To help you understand my database, here are the tables:

strgenrl (this is general information related to the store, such as address). The store number is the primary key in this table.

strlease (this is landlord and rent information pertaining to this store). The store number is the foreign key in this table.

strstrat (real estate strategy for the store). The store number is the foreign key in this table.

strops (operating data for the store). The store number is the foreign key in this table.

strcapex (capital expenditure information for the store). The store number is the foreign key in this table.

I have linked the store number in the strgenrl table to the store number in each of the other tables listed using a one to one relationship.

I feel like this is correct, but it just isn't doing what I expected it to do!

Thanks in advance!

 

[Len Boorman]

Just to jump in and share the load.

The other tables will not self populate.

When you have data to go into a related table part of the input is the value from the Store Table PK that is going to be the FK of the related table.

As an example

Leases

Perhaps not every store is leased ?. In this case you do not want a record in the lease table at all

Other stores you may want to keep lease history in which case you will have multiple records in the lease table for a given store.

These exact scenarios may or may not exist for your particular situation but they illustrate the rationale behind Relational Database Design and Normalisation.

Thijnk about your salary

You are paid many times and therefore there will be many records of salary against a specific person, but only one record of the person.

In summary the FK of related tables is populated on as "as required" basis cos you do not maybe know how many times you want to addition.

Having said that I have had situations whereby test results of something have been decreed as being every 15 mins over 5 hours. I have forced the creation of the time record for a test when the test itself is raised. This required a separate piece of code fired on the "Add Test" situation.

HTH

L

 

[KKilfoil]

Rule of thumb: If a piece of information about a store is unique, like an address, it belongs in the 'store definition table' (your [strgenrl]).

If a piece of information may occur more than once, it needs to be in a separate table, with a unique identifier, and use a foreign key to link it back to the store.


So, if you only ever have one 'real estate strategy' per store, it should probably go into [strgenrl] as well. However, if the strategy needs to exist more than once (say it evolves, and you have a requirement to keep the old versions), then you need a separate table. Although you can create 1:1 relationships between tables, the frequency is very rare, as you normally only do this to 'hide' sensitive info from certain users, or other rare conditions.

... assuming that you have already defined your relationships from the FKs in the other tables to the PK in the [strgenrl] table (using the relationships window):

The other tables will not be 'fed' from the main table. Records should only be created in your [strcapex] table, for example, as you need to add capital items. For illustrative purposes. use the form wizard to create a form/subform, include ALL of the fields from both [strgenrl] and [strcapex], ensuring that you choose the subform option at the appropriate step. Then try entering records into the form/subform.

After you have a few records in there, a few capexs for a couple of different stores, go back and look at the records that are now in these tables. You should see how the relationships take care of the 'keys' for you.