Splitting a table into two one-to-one

[Anchoress]

I want to remove 5 fields from my parent table in a contact management DB to a separate 1-1 table, and I'm trying to find information on the best way to do so. My main question is how to structure the relationship - does the primary key (autonumber) in my "main" parent table become the foreign key in the subsidiary 1-1 table?

I was thinking of using the copy command to create a copy of the table, and removing the duplicate fields from each table. Is that the best way?

If there is info I can read up on myself I would appreciate it - I don't need hand holding if there is already info somewhere - I don't want to abuse the goodwill of the excellent people on this forum.

 

[namliam]

The main question is WHY??

If you have a 1-1 relationship it is a rare situation where you would want to force a seperate table.

On subject...
Yes the PK (autonumber) of your main table would become the FK of the sub. This FK would also be the PK, it beeing a 1-1 relationship

I would really think twice before implementing this though...

 

[Anchoress]

Hi Namliam:

Thanks for the reply. It's not my choice. The people who use the DB, who use the tables exclusively and don't like forms for data entry, want these fields, which are no longer used but they want stored for archive purposes, gone.

Thanks for the info, but I don't understand what you said about the ID being the primary and foreign key at the same time. Is this possible? I've never done it.

 

[Atomic Shrimp]

Gotta agree with namliam. Why?

If you have a situation where... I dunno... the main table is just too big and confusing to work with, just create a query returning only those 5 fields you're interested in, then use that query as if it were a table.

Splitting the fields out into a separate 1-1 table is simple enough for the existing data, but you will have to maintain the integrity of the key for any records added in future - and that's something that could (well, probably will) go wrong.

 

[Atomic Shrimp]

Hi Namliam:

Thanks for the reply. It's not my choice. The people who use the DB, who use the tables exclusively and don't like forms for data entry, want these fields, which are no longer used but they want stored for archive purposes, gone.

Ouch. I would just refuse to support such a request, on the grounds that they're asking for something that makes the data more susceptible to failure, but maybe you don't have the choice...

Can you not give them a datasheet-style form based on a query that does not include the fields they dislike seeing? It will look like a table from their point of view, without compromising the integrity of the db.

Thanks for the info, but I don't understand what you said about the ID being the primary and foreign key at the same time. Is this possible? I've never done it.

I think what namliam means is that the the term 'foreign key' normally only appears in the context of one-to-many relationships - in a true one-to-one relationship, either table could be said to contain the foreign key - depending on which one you looked at first.

 

[Anchoress]

It's kind of frustrating to have everyone saying why why why. I don't have a choice about this, it is going to happen. A query returning relevant fields in the datasheet view won't work because they use the parent table to edit the child table. I don't know if more recent versions of Access support linked table editing in queries, but I'm using Access2000 and they can't do it in that version.

The fields being cut won't be edited again; may I ask how this change would affect the data integrity? My users who are asking for this just want the data being cut to be "archived" in a way.

 

[namliam]

It may be a 'pain' to ask why? But... in all honesty doing things like this that 'dont make sence' forces me (us) to ask these questions.

Users ask, dont tell... They want the columns out, take them out of the form
or hide them in table view
or make a query for them to use

It is simple enough to create the 1-1 table, just make a create table query to send the 5 fields to a new table and remove them from the old table, make sure to include the Primary key of your old table.

Because your FK will be unique (i.e. the 1-1 relationship), it can/will/should double as the PK for that table, it will still only be the PK of your main table.

A proper query, when properly joined and such is editable in any version. Problems come with bad queries and/or grouped queries.

IMHO
Users are users and will NOT determain how I as a developer maintain my database(s).
Your #1 problem is the users touching your tables directly, users are users and dont touch anything in my database. If they do and want fields "gone" from the table, they (or you) can hide the columns rather than move them to a 1-1 raltion table.

 

[Atomic Shrimp]

Sorry it's frustrating for you. Please understand that we're asking 'why?' because it's important for us to fully understand the situation before giving advice - advice based on a partial understanding of the situation could do more harm than good.

In your situation, I would make an archive copy of the entire table, calling it tablename_backup_090102 (or something like that), then simply delete the five fields from the live table. If you need to look at historical data that includes the five fields, you can look in the backup table and see the full records there. Backup the db to some removable media for good measure.
There's no need to back up just the five fields - back up the whole table, then edit the current one it to meet current needs.

(in my experience, however, it's often the case that someone will then ask you to put it back the way it was, or they'll come up with some bizarre request to be able to optionally see the five removed fields, or someone else will complain that something is broken - good luck!)

BTW, Access 2000 should let you edit a table through a straightforward query (as long as it's not a snapshot type, and doesn't include any complex joins or aggregation)

 

[Anchoress]

Thank you for all the useful advice.

To clarify - I think people are misunderstanding my users' preference for editing in the table - it is a parent table with a child table, and the child table is accessible through the + sign, which they use to see and edit all the child records. Hence, a multi-table query wouldn't suffice (Access2000 doesn't support multi-table query drill-downs, as far as I can see), and a simple replication of the parent table without the unwanted records would DEFINITELY not suffice.

As for the 'users ask, don't tell' - since you won't take my word for it, I have to fill you in on my whole life story - these are my bosses, and they are former mainframe developers. They think they know exactly what they want, and if I can't do it, they will fire me and find someone who will do exactly as they ask.

I very much appreciate the specific suggestions, because this is happening one way or another, or you will be seeing my resume on Monster.com.

 

[Anchoress]

In for a penny, in for a pound.

I spend 95% of my time doing other things besides database work - the main job I was hired for has nothing to do with data management. Both my bosses are former DB developers who know way more than I do but are too busy doing other things to do the work themselves. So I'm in the awkward position of being told what to do by people who don't trust me because, even though they want me to do the work, they don't accept my advice or input because they are the experts. They don't even have time to educate or support me in any way, so when they ask me to do something I don't know how to do, I have to go to books or the internet to figure it out. Which they also don't respect. They tell me what they want done, they expect it done, they fiddle with the DB however they want when they get the inspiration, and they work with the data exactly the way they feel like.

 

[Atomic Shrimp]

Thanks for the extra info, Anchoress - it can be horrible to be in a situation like yours, but the 'why' questions are just a necessary part of the process - not to be taken personally - it helps us to help you.

Personally - if I were in your shoes - I'd be exploring the job market regardless of the outcome of this issue (and I have been in a situation like that before - being told that I must do something in a specific - and wrong - way, then being expected to pick up the pieces when it all went wrong, then being shouldered with the inadequacy and unmanageability of the system going forward), but maybe there are other aspects to the job that compensate.

 

[Anchoress]

Mike: Thanks, but I worked very hard to get this job for the 95% that isn't data management - I'm not giving it up over something that takes up 2 hours a week of my time. I have learned to just shut my mouth and run to the internet whenever they ask for something. And speaking of... why would Access make it possible to create 1-1 relationships if they're so awful??

 

[Atomic Shrimp]

Fair enough - if it's only a small part of the job, I'd probably do the same as you - grin and bear it.

Access will let you do all sorts of things that aren't necessarily good DB administration (in fact, almost any DB will).

1-1 relationships aren't so much awful as they are unnecessarily complicated - they don't offer any advantage (usually, in a db designed to normal form) over a single table, at the same time as creating new opportunities for failure.

 

[namliam]

In for a penny, in for a pound.

.... They don't even have time to educate or support me in any way
.... they fiddle with the DB however they want when they get the inspiration

People like this are a pain, like you say, penny, pound, either you do or DO NOT do something.
Either you support this database OR someone else does. Having 2 captains on one ship is -ussually- not a good idea, asspecially when one of these captains feels supperior to the other.

Access will let you do all sorts of things that aren't necessarily good DB administration (in fact, almost any DB will).

Let me echo that... software doesnt enforce you to do the 'right' thing.... good practice does... as does development and 'normalisation' and stuff like that.

 

[Atomic Shrimp]

One thing I would recommend is to try to find a way to document situations like this when you get railroaded into doing something in a way you don't think is the right way.

Obviously it depends on your situation, but I have worked for bosses with whom I could argue a case, but it would usually end with them getting their way, however, on more than one occasion, when the solution they imposed failed in exactly the way I had argued it would, their memories of the debate were less than perfect and they remembered it with the roles reversed - and therefore it was my failure.

It's not always easy to document a disagreement like that without it looking like you're just going to use it to score points later - a lot depends on the personalities, sensitivities and relationships, but if you can, it may help later (you can even be working on a safety net in the background, in some cases).

 

[namliam]

and therefore it was my failure.

Sounds very familiar

 

[Atomic Shrimp]

I reckon it's probably pretty common - and in contexts beyond programming too - the older, more senior guy wants it done in some bizarre, arcane way, because that's how they did it back in the day (or just because the modern methods have moved too fast and left him behind) - and he keeps on doing this right up to (and beyond) retirement age - at which point it's not at all unusual for memory lapses to occur.

 

[MSAccessRookie]

In for a penny, in for a pound.

I spend 95% of my time doing other things besides database work - the main job I was hired for has nothing to do with data management. Both my bosses are former DB developers who know way more than I do but are too busy doing other things to do the work themselves. So I'm in the awkward position of being told what to do by people who don't trust me because, even though they want me to do the work, they don't accept my advice or input because they are the experts. They don't even have time to educate or support me in any way, so when they ask me to do something I don't know how to do, I have to go to books or the internet to figure it out. Which they also don't respect. They tell me what they want done, they expect it done, they fiddle with the DB however they want when they get the inspiration, and they work with the data exactly the way they feel like.

If you don't mind, I would like to make a minor adjustment to what you have stated.

Both my bosses are former DB developers who "THINK THAT THEY" know way more than I do...

Having spent 20 years working with Mainframes before joining the Access Crowd last Winter, I can tell you that the real truth is that both of your bosses are former DB developers who think like Mainframe DB Developers, and Access Databases should not be thought of in the same way.

Mainframe DB users are very often familiar with the structure of the database and the tables that they use the most. Some prefer to enter data through their tables as well.

Access DB users (other than power users or developers) are most often Form users and are not as familiar with the structure of the database and the tables that they use.
​

It is too bad that they have chosen to place you into a position like this.

 

[Atomic Shrimp]

Yeah, but it looks like the key word in all of it, for Anchoress is 'bosses'.

I agree though, it's too bad (for them too, really) that they choose to do it like that.

 

[Anchoress]

Thanks everyone for the advice and support.

I have decided to go with the suggestion of copying the whole table as an archive version.

I have a question though - I was hoping to keep the archived data in the same DB, but I don't want to add 30% to the size of my DB - would it be OK to trim the records that aren't needed? Only about 400 of the 3000 records in the DB are populated with the data my bosses want cut.