How to map several tables together?

Question

I am trying to figure out how to create a small database given the fields in the picture below.

 

I haven’t even see this data yet, but I’m told I will get something like what you see in the image.  I’m wondering how to choose a unique ID for each table.  I think the ID will be the orange field, but in case one of the tables don’t actually have this kind of ID, how can I create a unique ID from the data sets?  Maybe something like the left 5 characters of two fields concatenated together.  I’m just thinking out loud here.  Also, how can I map these tables together?  Will I need to create another table with all the IDs and map it that way?  I’m trying to figure out the best practice here.

Thanks!

---

MY BOOK

Answer 1

Hard to say without seeing some data, but having the first two fields and the last field in each table is almost certainly a mistake.

We also need to know something about how these entities relate. For example I suspect Personnel works as Facilities, so I would need to see a FacilityID foreign key in the Personnel table.

CO1 through 7 constitute a Repeating Group which should be spun off in its own child table.

Don't use spaces and other funny characters in a field name; those will cause notational difficulties.

I would approach this by modeling the tables independent of these data files, then import the data into the correct relational design.

---

-Tom. Microsoft Access MVP

Answer 2

Best practice would be to actually look at the data and find out their relationships. Anyone can guess, only you can answer. When you know the details you can come back here and ask further.

---

Best regards, George

Answer 3

Please see the image below.

The two orange fields (IDs) must be linked together.  There really isn't a universal ID common to all data sets, so the only way to do this is create a mapping table, based on the logic of the business hierarchy, right.  That's the only way to do it.

Any thoughts?

---

MY BOOK

Answer 4

Already answered:

I suspect Personnel works as Facilities, so I would need to see a FacilityID foreign key in the Personnel table.

I would approach this by modeling the tables independent of these data files, then import the data into the correct relational design.

---

-Tom. Microsoft Access MVP

Answer 5

Ok.  Got it.  thanks a lot!!

---

MY BOOK