Splitting a table into two one-to-one
- Thread starter Anchoress
- Start date
Atomic Shrimp
Humanoid lifeform
- Local time
- Today, 09:34
- Joined
- Jun 16, 2000
- Messages
- 1,953
As an alternative, you could archive the table into a brand new db, then link it back in - so it would be there if you need to run any queries to retrieve the old data, but won't be taking up space.
stopher
AWF VIP
- Local time
- Today, 09:34
- Joined
- Feb 1, 2006
- Messages
- 2,388
I would suggest using subforms. For example, you could have two subforms on a blank form. The first subform lists all the fields and records you want to see from your main table. The user can then click on one of the records and this will automatically show the relevant child records in the second subform. As well as the database integrity benefits already mentioned by others, you also get a slick professional design which can be tailored further as necessary.
Chris
wiklendt
i recommend chocolate
- Local time
- Today, 20:34
- Joined
- Mar 10, 2008
- Messages
- 1,746
i wouldn't say it quite like that.
i would say it more like: "they are arrogant ar$ehole$ who know nothing about how to use an access database (insist on using tables for data entry? what kind of 'expert' does that??)"
i agree with whoever it was that said that you should be looking in the wanted section anyway. i wouldn't even bother waiting till you finish these childish demands. i'd look for a job then say "cherrio" even if the changes haven't been done. what they're doing is either abuse or bullying, not sure exactly, but it's definitely one (or both). you should exlpain they're being pig-headed, and if they don't respond to that, and if there's someone higher you can officially complain to (and trust), do it. you should not be treated that way.
tell them to stick it. i'm so pissed off for you right now... i didn't even read the rest of the thread after i read that post - sorry, i could be a little more constructive... or helpful... anyway, sounds like the other users on this forum are doing a good job... good luck with your project, and life!
- i would use a form with a subform. the form being the main table you soke of at the beginning, and the subform being the child table they want to edit.
wiklendt
i recommend chocolate
- Local time
- Today, 20:34
- Joined
- Mar 10, 2008
- Messages
- 1,746
Oh yes, definitely.
- Local time
- Today, 09:34
- Joined
- Sep 12, 2006
- Messages
- 16,072
not sure if this has all been covered
a) the problem with entering data directly into a table is that it bypasses most validation/sense checks that would otherwise be intercepted by judicious form design. eg a value selected from a group of radio buttons, might allow entry of vlaues 1 to 4 - but direct entry into a table could allow anything to be entered. Data could be left null or blank where it is not appropriate - data could be deleted or modified directly after entry - if you present the data via forms, then it doesnt matter what is in the table - because the users dont see it - but if your bosses want something, and they understand the issues then no problem - just take the pragmatic view, as you are doing
b) 1 to 1 tables. Now. since by definition there can only be 1 and only 1 item in each table, it does beg the question why you would need 1 to 1 joins. The reasons I have seen are
i. too many fields, so the table has to be split
ii. more likely, a security thing. Say you have a payroll databases. Then you may have a 1 to 1 link joining general employee data, with payroll data. In this way, you make the general information (name address etc) available to all users, but keep the sensitive stuff (salary level etc) more private, and restricted.
a) the problem with entering data directly into a table is that it bypasses most validation/sense checks that would otherwise be intercepted by judicious form design. eg a value selected from a group of radio buttons, might allow entry of vlaues 1 to 4 - but direct entry into a table could allow anything to be entered. Data could be left null or blank where it is not appropriate - data could be deleted or modified directly after entry - if you present the data via forms, then it doesnt matter what is in the table - because the users dont see it - but if your bosses want something, and they understand the issues then no problem - just take the pragmatic view, as you are doing
b) 1 to 1 tables. Now. since by definition there can only be 1 and only 1 item in each table, it does beg the question why you would need 1 to 1 joins. The reasons I have seen are
i. too many fields, so the table has to be split
ii. more likely, a security thing. Say you have a payroll databases. Then you may have a 1 to 1 link joining general employee data, with payroll data. In this way, you make the general information (name address etc) available to all users, but keep the sensitive stuff (salary level etc) more private, and restricted.
Atomic Shrimp
Humanoid lifeform
- Local time
- Today, 09:34
- Joined
- Jun 16, 2000
- Messages
- 1,953
I'm working on a project at the moment that (I think) does genuinely require a one-to-one relationship.
I'm building a companion application for an access application provided to the organisation by the bank (we need some additional reporting functions, etc).
One of my tables is therefore a linked table from the other app, containing records describing people - but I need it to have more fields - for example email address and a few other fields pertinent to my companion app. I'm not allowed to write to the linked table, or open the other app in design mode to change the table structure.
So I'm making a supplementary table - local to my app, or its own back end DB - to extend the fields in the linked one.
That is, unless someone can suggest a better solution...
I'm building a companion application for an access application provided to the organisation by the bank (we need some additional reporting functions, etc).
One of my tables is therefore a linked table from the other app, containing records describing people - but I need it to have more fields - for example email address and a few other fields pertinent to my companion app. I'm not allowed to write to the linked table, or open the other app in design mode to change the table structure.
So I'm making a supplementary table - local to my app, or its own back end DB - to extend the fields in the linked one.
That is, unless someone can suggest a better solution...
well working with data from another application is one of the few things that may cause me to use a 1-1.
However using data straight out of that application to 'join' with your own data and use in an application is generaly not a good idea as it may cause a considerable drain on the 'target' system. Access pulls all records from said table if you try to join or search when using 2 different sources, I would strongly consider fetching the data once only into my own database/backend and working with ALL data there... Possibly still in a 1-1 relationship or even having 1 'enlarged' table where you update the data when changed from the 'mother' app.
Depending on the rate of change of the source data, you could choose one or the other. The lazy solution is the 1-1, just updating all records all the time.... If it is better, is another discussion... but still this IS definatly one exception where 1-1 would be an option (in my book anyway...)
However using data straight out of that application to 'join' with your own data and use in an application is generaly not a good idea as it may cause a considerable drain on the 'target' system. Access pulls all records from said table if you try to join or search when using 2 different sources, I would strongly consider fetching the data once only into my own database/backend and working with ALL data there... Possibly still in a 1-1 relationship or even having 1 'enlarged' table where you update the data when changed from the 'mother' app.
Depending on the rate of change of the source data, you could choose one or the other. The lazy solution is the 1-1, just updating all records all the time.... If it is better, is another discussion... but still this IS definatly one exception where 1-1 would be an option (in my book anyway...)
- Local time
- Today, 03:34
- Joined
- Feb 28, 2001
- Messages
- 30,556
Where I think we are missing the point is that Anchoress is saying that only older entries will have the 1/1 relationship. I.e. this is clearly an historical segment of the table.
If the following statement is true, then there is no violation and no problem in what is being proposed here.
1. The entries in the table at the time of the split have some extra data in the "supplemental" fields being moved to another place.
2. Newer entries in the table after the split will never have the extra data.
If this is true, then technically this isn't a real 1/1 relationship, it is a 1/many case because with respect to the newer entries, the historical table is SPARSE. It is not fully populated. In hard fact, for newer entries, that second table might as well not exist.
The relationship could legitimately be declared 1/many without a problem in any case. Just remember that INNER JOIN operations based on the newer table will not return records entered after the split, but an OUTER JOIN would do so. And that is true regardless of which table is the left side of the join and regardless of whether the relationship was declared 1/1 or 1/many.
Anchoress, the reason you have encountered flak from some of the members here is that a 1/1 key with one table being sparsely populated leads to issues of normalization from a purist's standpoint.
In "purely" normalized tables, the PK for a table uniquely identifies the record and all data in the record are selected by and depend on only the PK. I.e. the selection rules are bi-directional in their implication.
If there is a second table with the same key, the "purity" issue is that now you have data selected by the PK - and it is the same PK - but there is an implication of impurity because there exists data depending on an implied value - the date of entry of that record - which is NOT the PK.
That is, existence of the matching record in the history table now depends on something that isn't a PK. This is a violation of normalization rules, generally speaking. While you can certainly make such a database work, there is a taint on it that makes the underlying set theory questionable.
As a pragmatist, I would say go ahead and do it, but recognize that to use the tables together you will need to be very careful about the JOIN clauses needed for the operation. Only an OUTER join can fully rejoin the two parts correctly - and that induces good odds of having nulls in fields for which the OUTER JOIN found no matches. So you just escalated the cost of all future programming. (Which might be an argument that works when no other argument could be effective.)
Also you need to be aware that the FIRST TIME the bosses want to edit anything in the historical table, they violated their own rules. Make it clear that you intend to make that table read-only, to stay that way at least until two full days AFTER Hell freezes over. If they object, then tell them that the tables cannot be safely split if they still intend to edit anything. Send 'em my way. I'll explain their problems to them in short sentences.
If the following statement is true, then there is no violation and no problem in what is being proposed here.
1. The entries in the table at the time of the split have some extra data in the "supplemental" fields being moved to another place.
2. Newer entries in the table after the split will never have the extra data.
If this is true, then technically this isn't a real 1/1 relationship, it is a 1/many case because with respect to the newer entries, the historical table is SPARSE. It is not fully populated. In hard fact, for newer entries, that second table might as well not exist.
The relationship could legitimately be declared 1/many without a problem in any case. Just remember that INNER JOIN operations based on the newer table will not return records entered after the split, but an OUTER JOIN would do so. And that is true regardless of which table is the left side of the join and regardless of whether the relationship was declared 1/1 or 1/many.
Anchoress, the reason you have encountered flak from some of the members here is that a 1/1 key with one table being sparsely populated leads to issues of normalization from a purist's standpoint.
In "purely" normalized tables, the PK for a table uniquely identifies the record and all data in the record are selected by and depend on only the PK. I.e. the selection rules are bi-directional in their implication.
If there is a second table with the same key, the "purity" issue is that now you have data selected by the PK - and it is the same PK - but there is an implication of impurity because there exists data depending on an implied value - the date of entry of that record - which is NOT the PK.
That is, existence of the matching record in the history table now depends on something that isn't a PK. This is a violation of normalization rules, generally speaking. While you can certainly make such a database work, there is a taint on it that makes the underlying set theory questionable.
As a pragmatist, I would say go ahead and do it, but recognize that to use the tables together you will need to be very careful about the JOIN clauses needed for the operation. Only an OUTER join can fully rejoin the two parts correctly - and that induces good odds of having nulls in fields for which the OUTER JOIN found no matches. So you just escalated the cost of all future programming. (Which might be an argument that works when no other argument could be effective.)
Also you need to be aware that the FIRST TIME the bosses want to edit anything in the historical table, they violated their own rules. Make it clear that you intend to make that table read-only, to stay that way at least until two full days AFTER Hell freezes over. If they object, then tell them that the tables cannot be safely split if they still intend to edit anything. Send 'em my way. I'll explain their problems to them in short sentences.
Similar threads
