Combining tables and keeping relations

[Bangsadrengur]

Hi there,



I'm currently working on improving a database that was handed to me and my first thought as I looked through it was to combine a few tables as they should have been from the start.

It's proving a bit difficult (for me at least) since the tables I have to combine into one, 4 of them, have a one-to-many relationship to 5 tables that also need to be combined but this time into two tables.

The first four tables that need to be combined into one all have autonumbered primary keys and contain one foreign key. The five tables they are related to have a composite primary key and some form a many-to-many relationship between the first four tables (others extend the many-to-many relationship to a single table which has not be mentioned before).

I have already laid out the new database in a seperate access file as I would like it to turn out so I'm not set on keeping the old database, exporting to a new file as long as I can keep the data and relations is fine.

How can I work with this setup in a manner that keeps my relations right and is relatively quick?

Ps. This is my first post here, please point out if I'm forgetting to put in some info, am not clear enough, need to move my post or anything else standing in the way of bringing this to a conclusion

[orange]

Do you have a data model?
Are the tables normalized?

How did you determine "to combine a few tables as they should have been from the start."

[Bangsadrengur]

Do you have a data model?
Are the tables normalized?

How did you determine "to combine a few tables as they should have been from the start."

Thank you for a very quick response orange.

I'm not sure I fully understand what you mean by data model. I'm pretty sure the database was custom built and not after any external design if that is what you mean.

I'm pretty sure the database fulfills the first four normal forms but not the fifth. With the new design I'm going to exclude the fifth normal form as well (did some guessing about how many tables would be added with combination calculations and got about 20 extra tables if I would normalize to that level).

I determined that combining the tables would be cleaner from the fact that for example the four tables that I want to combine describe the same objects but are split up into four depending on where the general location of the recorded items are. My idea was to combine the tables and add a field that would reference a new table containing current locations.
The database describes wire connections mostly and the four tables mentioned describe wire pairs within cables. The next five tables describe how wire pairs connect through ,patches' (think that's the right english word for it) to other wire pairs (recorded in the first four tables) or to connection cabinets (which are listed in a single table).

[orange]

Third normal form 3NF is adequate for most of us. And then there may be some backing off that in order to attain/maintain performance.

By data model I mean a picture of your tables and relationships that can be maintained as the database structure changes.

see: http://www.databaseanswers.org/data_models/index.htm

and http://www.rogersaccesslibrary.com/T...lationship.zip

[Bangsadrengur]

Good to know that normalizing up to 3NF is common practice, was almost dreading that someone would point out that those 20 extra tables were necessary.

The database as it is now as well as my draft for changes are both set up with relations that have the referential integrity enforced through the relationship view in Access so by what I understood from reading the material posted above I should have a data model for both. Most of the design decisions were inherited from the original database but I've been spending some time getting to know how and why it was set up the way it was so that should not be a problem either.
If necessary I can post a picture of the part of the data model in question (though the labels are non-english so it would mostly be for visual aid).

So I think I'm safe to return to my original question on how I can combine tables, containing mostly the same info each, without other tables with foreign/composite keys based on autonumbered primary keys in the original tables loosing their relations.

[orange]

Typically, if one of these potentially "mergeable" tables has a 1:1 relationship to your base table, you could move the fields to your base table. Often these are separated, even though they share a 1:1 relationship, because it's a separate subject matter or a new feature that may not be considered permanent at this time.

Perhaps you should post some of the tables and data invovled to help others understand the details of your request.

[Bangsadrengur]

The image below my clarify some things, it shows relevant tables and their relations.
See: http://imageshack.us/photo/my-images...ofnlagnir.png/

[Bangsadrengur]

Managed to solve my problem.

Tried something again which I thought I had tested thorougly, guess this time I just did it right.

My problem was how to find a way to combine multiple tables that described the same object, all had auto-numbered primary keys (so they all went from 1 to something) while maintaining relations to multiple tables with foreign keys linked to them that would also be combined.

My solution was to remove the relations, change the auto-numbered primary keys to numbered primary keys. Then I put the old relations back up watching that they were set to cascade updates. By doing this I was able to change the primary keys in such a way that every table followed the next number where a previous table left off. Now I am able to merge what ever table I want to and not loose any relations.

Hope this is clearly put so other newbies like me can maybe spare some time if this comes up and follow the solution above