Table to SubTable relationships using Autonumber as Primary Key 1

[Quintios]

I have a main table with several subtables. When entering records in the main table, the autonumber datatype takes care of the primary key. When I enter a record that has extended data that needs to be entered in the subtable, how do I ensure that the Primary Key number is propogated to the subtable?

The reason I ask is because I can't edit the Autonumber field (as I'm sure you all are aware of) so I can't manually enter information into the main table, then pull up the subtable and enter the rest of the information.

Is this something that can only be done using a form/subform entry option?

Thanks in advance,
Q-

 

[930driver]

I believe I understand what your trouble is. It sounds like what you are doing is having the same key value for your sub tables as the key for your main table.

If this is the case you have a one-to-one relation between your main table and the subtables. This amounts to just one big table broken up into pieces. There's not much need for this type of an arrangment.

What is more commonly done is to have a one-to-many relationship whereby you can have several subtable records all related to a particular record in the main table. Perhaps this is what you really want.

To do this give each subtable it's own primary key (autonumber) and add a field with the same name as your main table primary key. In THAT field enter the primary key of the record from the main table. This is called a foriegn key and provides the relationship to the main table. If you use a form/subform to display the main record and the related sub records, and properly set up the subform (specify the name of the your main table primary key for the link fields) your foreign key in the subtable will be automagically entered.

Hope that helps a bit.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Quintios]

Well, yes and no. The current database used for this application was one big Excel spreadsheet, and a couple other Word documents supporting the spreadsheet. I bought the book by Hernandez, "Database Design for Mere Mortals" and followed it to a T (or at least tried to.) According to the book, when there are groups of fields that only apply in certain circumstances (i.e. are empty) then they should be split out into subset tables. In my case only one subset table is used per record; you'll never have a record in the main table refer back to more than one subset table. Maybe I misunderstood the book (I'll go back and read it again) but from what it said, the Primary Key links the Main table to the subset tables, without mention of a foreign key (the primary key kind of doubles as the PK and the FK in the subset table.)

So, if I keep my subtable arrangment and add a 'Number' field to the subset table to refer back to the main table, that should do it? The concern I have is that I want the number to be automatically propogated from the main table to the subtable. I don't want the enduser messing with primary keys, foreign keys, or anything like that. Will the form/subform copy the key from the main table to the subtable?

Thanks for your earlier response,
Q-

 

[930driver]

I can't quite picture what you are doing here. This concerns me because it doesn't make sense.

If you have several fields, each of which would only be filled in for a particular record, could it be that those fields are describing the same thing? For instance "Color" with a yes/no field for Red, White and Blue. One record is Red, another White, and yet another Blue. If that is the situation I'll urge you to stop and regroup. Your database would call for a one-to-many relationship between Color and Main (in my example the one side is Color, but I'll leave _why_ for another message, if needed).

I might be wrong in what it sounds like you are trying to do, but in any case, perhaps you can provide some details about your main and sub tables (and the fields that you want to split out) so I (or anyone else that might be following this thread) can ponder on a proper design.

One interesting experiment you could try would be to import your 'one big Excel spreadsheet' into a table and run the Table Analyzer under Tools: Analyze: Tables. It is far from perfect, but could give you some ideas, as it will attempt to identify potential look up tables and child (sub) tables.

By the way, I hope you won't be hasty in settling on a design. Take the time to get it right the first time and you will save yourself many headaches in the future.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Quintios]

OK, if you're willing to read a really long message, here goes...

The database I'm creating will hold information about program variables. I write computer code for a propriatary computer that controls equipment in chemical plants. Each variable has certain properties that pertain to all the variables, and some variables have extended properties that pertain only to one type of variable. In the database, these variables are referred to as 'Tags'. The extended information that specifies the type of tag can be referred to as 'AlarmTags', 'InputTags', 'OutputTags', and so on. Not all variables have this extended info, so there will be many records in the main table that do not have related records in the subset tables, as they aren't needed.

According to the database design book that I read, when you have a table that will contain empty fields, and those are always empty for certain records, you should split that table out into a subset table.

So, I have a main, or parent table, called 'Tags'. There are several subset tables that provide more information about the record in 'Tags' as necessary (see above).

According to Mike Hernandez, the author of the book I'm reading (he's a really nice guy and responds to my emails, pretty cool!) this would be the proper setup for a table/subset table combination (PK means primary key, FK means foreign key, the other fields are just there to show that there's more information):

Tags AlarmTags
---------------------------
TagID PK-|----|-TagID (PK/FK)
TagName AlmDef
ModName AlmGlos

The TagID in the 'Tags' table and 'AlarmTags' subset table is the Primary Key for both tables as well as the Foreign Key for the subset table. There is a one to one relationship because only one record in AlarmTags relates back to the main table 'Tags' and vice versa.

Maybe this is just theory and not practice. I don't know, this is the only book I've read...

Thanks again for your responses,
Q-

 

[montrose]

Qunitios, 930driver has suggested you re-look at your design for one to many relationships. I think he's right on target. You said, "Each variable (ONE) has certain properties (MANY) that pertain to all the variables, and some variables (MANY) have extended properties (MANY) that pertain only to one type of variable. In the database, these variables are referred to as 'Tags'." Looks to me like: One 'Tag'(variable) to Many properties.


Tags AlarmTags
---------------------------
TagID PK-|----|-TagID (PK/FK) Where's your foreign key
TagName AlmDef here?
ModName AlmGlos

Seems like it should be:

Tags AlarmTags (Properties??)
---------------------------
TagID PK-|----|-AlarmID PK
TagName TagID FK
ModName AlmDef
AlmGlos


"The TagID in the 'Tags' table and 'AlarmTags' subset table is the Primary Key for both tables as well as the Foreign Key for the subset table". I think if you revisit Mr. Hernandez you'll find that nowhere does he say one primary key is used as the primary key in two different tbls. Concur with 930 driver, this doesn't make sense. If you use a primary key from one tbl in another tbl, that primary key from the first tbl becomes the foreign key in the other tbl. And this statement is very confusing, "There is a one to one relationship because only one record in AlarmTags relates back to the main table 'Tags' and vice versa". That's not what you said here ""Each variable (ONE) has certain properties (MANY) that pertain to all the variables, and some variables (MANY) have extended properties (MANY) that pertain only to one type of variable. In the database, these variables are referred to as 'Tags'." ! Keep thinking about this and bringing your ideas back. I'm sure either myself or 930driver can help you get this sorted out. HTH, Montrose Learn what you can and share what you know.

 

[Quintios]

Montros said (with small edits from Q in brackets:

"I think he's right on target. You said, "Each variable [record] (ONE) has certain properties [fields] (MANY) that pertain to all the variables, and some variables [records] (MANY) have extended properties [fields] (MANY) that pertain only to one type of variable. In the database, these variables are referred to as 'Tags'."

I'm a bit confused about the above statement because you seem to be mixing RECORDS and FIELDS with your MANY and ONE comments, thus I added more info. One variable is a record, and the properties of that record are the fields, or columns, for that record. Maybe I misspoke in the previous post I made. If that is true, I am sorry for the confusion.

The thing is that the subset table record is UNIQUE to the record in the main table. There is a one to one relationship here. A record in AlarmTags will refer back to only one record in Tags, and vice versa.

The reason I split out the AlarmTags information from the Tags table is as I mentioned before. If this were one big excel spreadsheet (as it is now) there would be many fields empty. If you have a record that refers to an InputTag, all the columns in this excel spreadsheet that pertain to AlarmTag information would be empty. According to the book, these empty fields should be split out into a subset table (page 195 'Establishing Subset Tables').

My original problem was getting Access to stuff 'Autonumber' into the subset table. From what I understand, if I use a form/subform and have the Master and Child properties set correctly, it will force the new record in the subform to take the same autonumber-created number in the subset table. Correct?

Here's more of the database (very very short version):

Main Subset Subset Subset
Tags AlarmTags InputTags OutputTags
-----------------------------------------------------
TagID TagID TagID TagID
TagName Latch Type IFDS P&ID Number
Value Setpoint Abort Action SIS
ScaleFactor Notes Justification No Test
Glossary Deadband True State IFDS Action
Units Delay Time False State Min Position

So, in the big excel spreadsheet, if I was putting information in for an InputTag, all the columns pertaining to OutputTags and AlarmTags would be empty. This, from what I understand, is why you create subset tables. The TagID is both the primary and foreign key in the subset tables.

***Mr. Hernandez was kind enough to reply to an email I sent, and *he* is the one that stated that the Primary Key and the Foreign Key are one and the same in a subset table.***

Onwards,
Q-

 

[Quintios]

I forgot to say THANKS for your responses, so here it is:

THANKS!! ;-)

Q-

 

[930driver]

I now see what you are trying to accomplish. Not having empty fields is a good idea and Mr. Hernandez's method certainly is one way to do it, however, with respect to Mr. Hernandez, it is still not fully normalized. What you really have is a many-to-many relationship between your Tags and their respective extended properties.

By the way, one thing hampering our conversation is the definition of terms like variables/records and properties/fields. You are correct that fields could be considered properties of a record in the sense that a record is a database object (in the design sense, not the Access sense).

I faced your problem a couple of months ago when I started a database to track network devices. A computer record needs some fields that a hub doesn't need. A hub needs fields that a server doesn't need, and so on.

In short we have an device table for fields that all (or at least almost all) devices share such as location, ip address, etc. Then we have (in your terms) an extended properties table with all the possible choices. These are the many tables. To join them there is a link table in the middle with a foreign key from each to establish relationships. All many-to-many relationships are joined with a link table in this manner. It is simple in design and I reduced the number of tables needed by maybe 25%. However it is complex in implimentation with dependent subforms and combos. We needed a 'device type' field (here is a one-to-many relationship) to limit the number of available extended properties during data entry to those appropriate to the device.

I am not going to attempt to explain how to apply this solution to your situation. I could do it in an hour if we sat down with sketch pads and could talk, but here, with just text and hours between questions and answers it just isn't practical.

So, back to your original question.

Give each of your subtables it's own independent primary key (autonumber) and another field (foreign key, long integer) called TagID. Then build a sub form based on the subtable. Link the subform to the main form using TagID and your foreign key will be inserted by Access. You'll do this for each set of extended propterties. For comparison, the many-to-many method only requires one subform and one extended properties table, where your's will require a different one for every tag type.

One thing you will want to do is have a TagTypeID field in your main table. Use a combo on the main form to lookup against a table called TagTypes with primary key TagTypeID and a descriptor. This tag type can then be used to conditionally display the appropriate subform based on which type you are working with (once again perhaps to much for this forum, or at least this thread).

Here's the gotcha with Mr. Hernandez's design. If you ever get a new tag type (and please don't tell me it'll never happen, been there, done that) you'll find your self adding new tables, redesigning forms etc.

In short, you can do what you are describing if you use a foreign key in your subtables that is not the primary key. It isn't normalized and could cause troubles down the road when you need to add a tag type. However it is easier to put together at first and might be a good solution in your case.

If my description of how to do what you want isn't clear let me know.


"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[930driver]

There is a simpler way to do what you want. I just tried to post it but it got lost. I have to leave now but I will try again tomorrow morning. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[930driver]

Sometimes just rereading a message gives different ideas. Pretty much as soon as I posted last night I realized that there is a fairly simple way you can do what you want. You'll still have all the potential problems inherent in a de-normalized data base but you won't need subforms.

If you set up your subtables with a primary key of TagID then you will have the one-to-one relationship with the main table (just rehashing). Then, using a query, join the tables and use that as the recordsource for a form.

Two things; you will have to programmatically set the TagID of your subtable and you'll need different forms for each tag type. The latter is because of your de-normalization and will be a hassle when you need to add a tag type. The former you can do in the AfterInsert event of your form. Just make sure both primary keys are in the recordsource for the form (you'll have to alias one of them if they have the same name) and set the subtable primary key equal to the main table primary key.

I object to this method that I just described, but it's another way to do what you initially wanted to accomplish.

Best of luck, no sarcasm intended "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Quintios]

OK, *NOW* we're getting somewhere. The book I mentioned about database design never mentioned normalization. I assumed this was because the author didn't want to confuse the reader with terms that weren't necessary, because if you follow the author's process you would end up with an inherently normalized database. That was my assumption.

You're right about the subset tables. I have to have a subset table for each tagtype. Fortunately, not every tagtype has "extended" properties and can fit nicely in the Main table.

Now, if you really feel like being EXTREMELY helpful (as opposed to merely being VERY helpful), why is this database de-normalized? Are you telling me that the tables and the subset tables should all be in one big fat flat file table? It seems that it would be inefficient to have a bunch of blank cells for each record. In addition, I think it would require a lot of forms with redundant controls to describe each type of tag, as opposed to having one main form and a little subform that pops in to finish out the description of that record. (Make sense?)

Thanks again,
Q-

 

[Quintios]

Woah, sorry. It seems that your lost post got posted anyway. Let me go read it first.

Q-

 

[Quintios]

OK, I read your post above (the one where you refer to the book I was reading) and I have to say you have a really good grasp on what I'm doing. I'm familiar with linking tables (I have one right now in the database), however, because each of the subset tables only refers back to one and only one tag in the main database there's no need for a linking table. From what I understand, linking tables are for when you can have multiple records in one table refer back to multiple records in a second table. By setting up the linking table you end up with two one-to-many relationships as opposed to one many-to-many relationship. In my case, we have, most definitely, a one to one relationship. The "extended properties", as I refer to them, only apply to one record in the main table.

At this point in time I'm designing the user interface and was actually figuring out how to do exactly what you described above. I have one large form with a small "window" in the bottom for a subform. Using a control group with radial buttons, the enduser picks a tag type and will set the 'visible' property of the appropriate subform to true and the rest to false.

Here's a question, if a user decides to make a tag into a different tag type, how would I delete the record from the old subset table (automatically)? What I mean is that I'd like to delete records from subset tables if the user indicates that the subset table information no longer applies to the tag type in the main table, as indicated by the changing of the selection of the tag type in the main table.

What I've come to realize as well is that the enduser won't want to work on all tags all the time. What they'll want to do, most likely, is to filter out everything but one type of tag and then work from there. I imagine that it won't be difficult to set the main table to a query and then go from there.


Q-

 

[Quintios]

I misspoke, each subset table refers to only one tag in the main table, and that tag in the main table only refers to the information in the subset table. One to one.

Onwards,
Q-

 

[930driver]

You misunderstand what I'm saying about a many-to-many relationship. You clearly have a one-to-one relationship the way you are set up. However, it is improper design. In concept you have tags and extended properties. The same extended property can apply to many tags and each tag can have n number of extended properties. A many-to-many relationship. But, like I said, this isn't the place for me to teach you how to set it up for your situation. Since you seem to be pleased with your current method you should probably carry on.

On the subject of changing tag type: one way would be to have a 'Change Type' button. It could work this way: open a popup form which allow you to select the new tag type (combo), next it would open the appropriate subtable (recordset in code), find and delete the old extended properties record, next open the subtable for the new extended properties record and create a record, insertng the correct key value (doubling as the foreign key in your case).

Pay attention to my earlier post on how to establish the links for your subform and you should be on your way.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[930driver]

By the way, I can't imagine a book on database design that doesn't mention normalization! Normalization is of paramount importance to database design. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[Quintios]

In the book's defense, I did skip some parts here and there and maybe it was mentioned, however, I don't think the use of the actual word "normalization" is important, which is what I was trying to say earlier. Give me a little leeway here while I explain. If you tell someone to design a database by doing this this this this and this, and you'll end up with a properly designed database, does it matter if someone actually states that it corresponds to fifth normal? (Or however you would state that.) It's like saying you can't drive a car unless you call a steering wheel a steering wheel. If you turn it and it gets you there, you can call it whatever. ;-) This is not a flame or an argument, and I completely agree that the concept of normalization is important.

OK, let's see if I can understand what you're telling me about the design. I truly want to understand before I go on because I know if you spend time up front doing it right you'll save a lot of time and misguided effort on the back end.

I'm reading and re-reading and I don't seem to get what you're saying. When I refer to extended properties, I'm referring to the fields of a given record, or tuple (if that's the correct term.) When you say that the same extended property, or field, can apply to many tags (but not all tags of course) you are correct. However, the information that would go in that property is not necessairly the same all the time, or even some of the time. Take for instance something called an AlarmTag. The AlarmTag has a field called 'Measurement'. The Measurement for a given AlarmTag is unique. You would not have two AlarmTags looking at the same Measurement. That would be redundant and unnecessary. So the field 'Measurement' is specific to AlarmTags and not any other type of tag.

How would you have designed it? If you have an existing flat-file spreadsheet the covers every piece of information needed for your purposes, would you have broken it up as I did and used a separate primary key/foreign key for subset tables, or would you have left it as one big table?

In retrospect, it might be much easier to design the user interface based of simply a query of the tagtype and then using the appropriate form. Being a beginner with this and reading good reviews of that book I just followed it step by step.

I do want to understand. I hope this is not becoming too exhausting for you.

Q-