Breaking up a table

[nsns1972]

I would like to break a really large table into a couple of smaller tables within the same file - without having to rebuild the entire thing from scratch. There are 166 defined fields in one table and it's too cumbersome at this point. Instead of designing and importing into a new one, I would rather just break the table into 2 tables and keep what is already there.

 

1. Is this possible?

2. What's the best way to do this?

 

I appreciate any help that you can offer.

[AHunter3]

Not a lot of fun no matter how you go about it.

 

You can import as a means of creating the new table with fields in the new table (save you some time in Field Definitions), move your relationships, and then delete the fields in question from the old table easily enough but

 

a) every relationship that previously pointed to the orig table, using fields that you've moved to the new table, will have to be manually repointed so as to use the new table. That will not, unfortunately, fix every reference that was previously a reference to OldTable::FieldWhatever so that it is now a reference to NewTable::FieldWhatever. Instead you're more likely to get NewTable::TotallyWrongField, which you will have to correct. So you're in for quite a bit of script-editing and layout-editing.

 

b) You didn't explain why you're doing this. I assume it's because data that was previously all of a one-to-one correspondence is now to be a one-to-many arrangement. I've done that. (What was once a "job" got broken up into a "job" which had multiple "products". Many fields that were previously "of the job" became "of the project"). Assuming such is the case, you have to rethink a great many of your data entry and printout layouts, as they will all assume a "flat" structure, and that will no longer be appropriate. Many of your printouts you will want to take place from the "many" side, perhaps as a list view with the parent "one" side fields in the header and/or footer areas. On data entry screens you'll probably want to rearrange your fields from the "many" side of the split so that they are all grouped together, and put them in a portal. Etc.

[nsns1972]

Originally, I re-did our "database" so that it would actually be a database, instead of the large table that it had been. There were 166 fields in the one table, everything from contact info to information about the different clients that we work with. It was a big mess. The database that I re-did, somehow became corrupted, so instead of redoing everything again, I am going to re-design the original table - and want to break it out so that it is more manageable.

 

Currently - there is only 1 table, with no relationships to anything. I would like to add a table - "Media Contacts". Giving me 2 tables - Media Contacts and Client Info. I need to take the Media contact info that is in the current table and move it to the new table and then add the relationship to the Client Info table.

[AHunter3]

Right. And presumably each Client Info record will have multiple Media Contacts records. Perhaps even also vice versa (a many-to-many relationship). All that I said above applies to your situation. If you have a many-to-many situation (each Media Contact having multiple Clients and each Client having multiple Media Contacts) you'll also have to run a script to create non-duplicate records in your new table instead of simply importing them, and assigning the foreignkey values as a temporary multikey, and then turning around and creating a third table, a join table where each rec is the confluence of one Media Contact and one Client.