Flat to Relational Conversion

[Cheese]

When converting a flat database to a relational one, I was told by a friend
to first make tables of the values on the "one" side of the relationship. I
did this - those tables are populated with unique values. Now all I have left
is to fill the "many" tables and connect those records with the appropriate
"one" values in the other tables. My flat database is still fully intact.

I'm confused how to perform the append query so that each record stays
intact throughout the new set of tables. Where do I setup the
relationships...in the append query or in the database itself? What else do I
need to specify in the append query for this to work successfully?

 

[John Vinson]

I'm confused how to perform the append query so that each record stays
intact throughout the new set of tables. Where do I setup the
relationships...in the append query or in the database itself?

BOTH.

The relationships defined in the database Relationships window are to
maintain "referential integrity" - that is, to ensure that you cannot
(no matter how you try!) enter an "orphan" record in a child table.

You need to join the tables in the Append Query in order to populate
each child table.

What else do I
need to specify in the append query for this to work successfully?

Without knowing your table structure in detail I can only speak in
generalities; but typically you would have run an Append query using
the "Unique Values" property to populate the "one" side table (or the
"one-est" table of several, more likely). This table should have some
unique ID as its Primary Key. This ID might or might not have been
part of the wide-flat table.

If it is, you're in clover: just create an Append query based on the
wide-flat, selecting the ID (to append to the foreign key field in the
child table), and whatever fields appertain to the child table.

If it isn't (much more likely :-{( ) you'll need some trickery. There
should be SOME combination of fields (hopefully fewer than ten) which
uniquely identify a "One" table record in the wide-flat table. You can
create a query joining your new "One" table to the wide-flat table,
joining *by these fields*; your Append query can then pick up the new
unique ID from the "One" table, and the other fields for the child
table.

You'll need to repeat this process for each of the child tables.


John W. Vinson[MVP]

 

[KARL DEWEY]

Make sure you have set the Key on your one side table - in table design view
click on the KEY field and then on the key icon on the tool bar - save.

Create your many table.
Click on the menu TOOL - Relations.
Pull down your one table - it should have the key field in bold.
Pull down your many table. Click on the bold key filed of the one table and
drag to the many table field that matches.
Click on referentail and cascade update related fields. Save.
NOTE - Read up on cascade delete before using.

In design view create your append query using you one table and source table
(flat database). Click on the bold key filed of the one table and drag to the
source table field that matches.

Run the query.

 

[Cheese]

When I made the "one" tables, I did so without any primary keys in effect. I
selected the Unique Value Property as you suggested. This leaves me with
several tables independent of each other or any other table. Was this the
wrong way to begin?

Perhaps this is from my lack of understanding with Access, but how does the
Append Query match up the already populated "one" tables to the unpopulated
"many" table if autonumber fields are used as the primary key?

Thanks for being patient with me, designing a database by working backwards
is much harder than I thought. Sometimes I'm just slow to learn new things
too.

 

[John Vinson]

When I made the "one" tables, I did so without any primary keys in effect. I
selected the Unique Value Property as you suggested. This leaves me with
several tables independent of each other or any other table. Was this the
wrong way to begin?

Probably - but you haven't lost anything yet.

The FIRST thing to do (and you may have at least partially done this)
is turn off the computer, leave the room, find a pad of paper and a
pencil, and make a cup of tea or coffee or other suitable beverage.
Sit down and think through the relational design: identify the
"Entities" represented in your wide-flat table (I'll call it WF for
short). Each Entity is a real-life person, thing or event; and each
type of Entity gets its own table. Determine which fields in WF belong
in each table; be sure that the ONLY fields that ever occur in two
tables are linking fields - unique in one table, a nonunique foreign
key in the other table.

Every table MUST have a Primary Key field. This might be a unique
identifier that you already have (e.g. a CustomerNumber, EmployeeID,
whatever - I don't know what your database has). If there is no such
field you may want to create your own, either an Autonumber or a
manually or programmatically maintained numeric key. If you use an
Autonumber as a primary key in a "one" table you should have a Long
Integer Number field in each related table as a foreign key.

Perhaps this is from my lack of understanding with Access, but how does the
Append Query match up the already populated "one" tables to the unpopulated
"many" table if autonumber fields are used as the primary key?

The Append query doesn't - not by itself. YOU need to do so in the
design of the query. There's nothing IN the "many" tables to be
matched when you start; the Append query will be taking data from WF,
or more likely from WF and the newly created "one" table, and writing
that data TO the new table.

I'd write out an example but it would take a long time and might not
help much if it's utterly different from yours - could you perhaps
post a (partial or complete) field list of your WF table and indicate
what new tables you want to populate? Perhaps an example using your
data would be more useful than an abstract discussion.

Thanks for being patient with me, designing a database by working backwards
is much harder than I thought. Sometimes I'm just slow to learn new things
too.

I've done this "working backwards" a few times, and IME it's usually a
LOT more work than designing from scratch, unfortunately!

John W. Vinson[MVP]