Table Design & Relationships

[Tom Bock]

I need some help with the design of tables structure and their
relationships.

The following is a generic outline of the tables and some example data:

***************************************************

TABLE_DIVISION
==============

PKID_DIV DivisionName
-------- ------------
1 Division1
2 Division2

***************************************************

TABLE_BRANCH
==============

FKID_DIV PKID_BRANCH BranchName
----------- ---------- ----------
1 1 Branch1
1 2 Branch2
2 3 Branch3

***************************************************

TABLE_TASK_BRANCH1
====================

FKID_DIV FKID_BRANCH PKID_TASKNO_BRANCH1 TASKNO_BRANCH1
-------- ----------- --------------------------------
------------------------
1 1 1
1
1 1 2
1.1
1 1 3
1.1.1
1 1 4
1.1.1.1
1 1 5
1.1.1.2
1 1 6
2
1 1 7
2.1
etc.


TABLE_TASK_BRANCH2
====================

FKID_DIV FKID_BRANCH PKID_TASKNO_BRANCH2 TASKNO_BRANCH2
-------- ----------- --------------------------------
------------------------
1 2 1
1
1 2 2
1.1
1 2 3
2
1 2 4
2.1
1 2 5
2.1.1
1 2 6
2.1.2
etc.


TABLE_TASK_BRANCH3
====================

FKID_DIV FKID_BRANCH PKID_TASKNO_BRANCH3 TASKNO_BRANCH3
-------- ----------- --------------------------------
------------------------
2 3 1
1
2 3 2
1.1
2 3 3
2
2 3 4
3
2 3 5
3.1
etc.

***************************************************


Essentially, I will have the following:
- Multiple division which have
- multiple branches which have
- multiple tasks numbers (plus other fields)


As expected, the task numbers might be unique in some branches while they
also could be found in more than just one table.

For instance:
- Division1/Branch1 may be the only div/branch that does "Research"
- while Division1/Branch1 as well as Division1/Branch2 does some "Admin
Work"


So my questions are:
- How do I construct the relationships (primary and foreign keys) between
these tables?
- Obviously, I must be able to query on e.g. "find all divisions/branches
that do ADMIN WORK" (which there are multiple div/branches).
- What are the data types for any primary or foreign keys? (autonumber vs.
number)?
- IMPORTANT... the TASKNO_BRANCH field must be a "text field" (multiple dots
e.g. "1.1.1.1")


Any suggestions are highly appreciated!!!


Tom

 

[Tim Ferguson]

For instance:
- Division1/Branch1 may be the only div/branch that does "Research"
- while Division1/Branch1 as well as Division1/Branch2 does some
"Admin Work"

So there is a many:many relationship between Branches and Tasks

- How do I construct the relationships (primary and foreign keys)
between these tables?

You need a new table BranchDoesThisTask, with the PK of the Tasks table and
the PK of the Branches table. (Sorry, I could not make head or tail of your
naming convention. Doesn't matter as long as you can understand it.)

- Obviously, I must be able to query on e.g. "find all
divisions/branches that do ADMIN WORK" (which there are multiple
div/branches). - What are the data types for any primary or foreign
keys? (autonumber vs. number)?

SELECT ALL BranchNumber, DivNumber
FROM BranchDoesThisTask
WHERE TaskCode = "ADMIN"

.... or whatever the keys are. You get the picture.


Hope that helps


Tim F

 

[tina]

well, you shouldn't have a separate task table for each branch. every time
you added a new branch, you'd have to create a new task table for it - bad
design. instead, suggest

tblDivisions
DivID (PK)
DivName
anything else that describes the division

tblBranches
BranchID (PK)
BranchName
DivID (foreign key from tblDivisions)
anything else that describes the branch

setting up tasks requires a little more thought. are the tasks
"predefined" - not the result of runtime input? do you need to list the same
task more than once for the same branch? or prevent the same task from being
listed multiple times for the same branch?
the following example is for predefined tasks, and a table that allows the
same task/branch in multiple records.

tblTasks
TaskID (PK)
TaskName
anything else that describes the task

tblBranchTasks
BTID (PK)
BranchID (foreign key from tblBranches)
TaskID (foreign key from tblTasks)
anything else that describes this specific instance of this task in this
branch

with this setup, you can add a division, branch, task, and task-in-a-branch
whenever you need to - without changing the table/relationship design.

hth

 

[Tom Bock]

Tina,

thank you so much for your feedback. Your suggestions made the design
of the relationships really easy!!!

For testing purposes, I have created a form (with underlying subforms)
that allows me to enter the information and automatically create the
appropriate autonumbers. That works great!

However, I now have another question as to how I can transfer large
volumes of data (from Excel into Access) into the individual tables.

Below the ****s, I have provided some sample data that I copied from the
4 tables (after entering the the testing data via the FORM).


SOURCE DATA INFO:
- substantially large spreadsheets that contain only the tasknames e.g.
"1.1.1"
(plus other additional information) . I easily could add one column that
contains
the "division name" and another column that contains the "branch name". Not
sure
if this would do any differnce though.


PROBLEM:
- My problem is that I don't know how to automatically create the
relationships (autonumbers) for the "BranchIDfk" and "TaskIDfk" in the
"tblBranchTasks".


QUESTION:
For instance, by loading the divisions, branches, tasknames into their
respective tables, how would I be able to create the "04001 = 02001 = 03001"
relationship in the "tblBranchTasks" (as shown below)? I anticipate that I
would
not be able to load them without some type of error message indicating that
I can't import them w/o the proper foreign key, right? Any suggestions?


Thanks in advance,
Tom





************ SAMPLE DATA ****************

tblDivision
===========
DivisionID Division
01000 DIV 01
01001 DIV 02


tblBranch
=========
BranchID DivisionIDfk Branch
02001 01000 BRANCH 01
02002 01000 BRANCH 02
02003 01001 BRANCH 03


tblTasks
========
TaskID TaskName
03001 1
03002 1.1
03003 1.1.1
03004 2
03005 1
03006 2
03007 2.1
03008 1
03009 1.1
03010 1.2


tblBranchTasks
==============
BranchTaskID BranchIDfk TaskIDfk
04001 02001 03001
04002 02001 03002
04003 02001 03003
04004 02001 03004
04005 02002 03005
04006 02002 03006
04007 02002 03007
04008 02003 03008
04009 02003 03009
04010 02003 03010

************ SAMPLE DATA ****************

well, you shouldn't have a separate task table for each branch. every time
you added a new branch, you'd have to create a new task table for it - bad
design. instead, suggest

tblDivisions
DivID (PK)
DivName
anything else that describes the division

tblBranches
BranchID (PK)
BranchName
DivID (foreign key from tblDivisions)
anything else that describes the branch

setting up tasks requires a little more thought. are the tasks
"predefined" - not the result of runtime input? do you need to list the same
task more than once for the same branch? or prevent the same task from being
listed multiple times for the same branch?
the following example is for predefined tasks, and a table that allows the
same task/branch in multiple records.

tblTasks
TaskID (PK)
TaskName
anything else that describes the task

tblBranchTasks
BTID (PK)
BranchID (foreign key from tblBranches)
TaskID (foreign key from tblTasks)
anything else that describes this specific instance of this task in this
branch

with this setup, you can add a division, branch, task, and task-in-a-branch
whenever you need to - without changing the table/relationship design.

hth

----------- ----------------------------------------- ----------------------------------------- ------------------------------

 

[tina]

you just had to throw a monkey wrench in the works, didn't you? <g>
importing data like that is always a royal pain, but usually it can be done.
hopefully you're migrating data from an "old" Excel format into the new mdb?
and once it's migrated you won't have to do it again?
the difference is: if you're doing a one-time migration, you can do it more
or less manually - doing some things "by hand" can be faster than figuring
out how do automate a complicated and multi-stepped process. but, if you
have to do this routinely, on a regular basis - it's better to figure out
how to get all the data in "correctly", then figure out how to automate each
step so it works right every time.

i could sit beside you and work out a successful import with you, tackling
the various issues as they arise. but in a post....with theoretical tables
and spreadsheets and data.....that just defeats me, sorry! if you haven't
come up with a solution, i'm willing to look at your db and data and try to
help. if you want me to, do the following:

put some representative "dummy" data into into a spreadsheet - doesn't need
to be a lot of records.
make a copy of your db, again with a little representative "dummy" data.
include the forms/subforms you built. Access97 thru Access 2003 is fine.
compact the db.
zip the db and the spreadsheet into a zip file (i have WinZip on my pc to
unzip).
email to me at ttacc_kill_all_spam_ess1 at yahoo dot com,
removing the underscores and kill all spam. copy this entire post and paste
into the email text, for my reference.
i'll try to set up a successful import, and email it back so you can see how
i did it.

tina

 

[Tom]

Tina:

I just emailed you the db & xls plus my comments.

As stated in the email, I truly appreciate your help in this matter.

Thank you sooo much!!!


Tom

 

[Tom]

Tina:

You have simply been awesome!!!

In case someone reads this...

I am working on a project that required some automated data imports. I had
no clue how to efficiently design a table structure and import routines.

And then Tina came along....

So, here's my reply to you:
Tina, not only have you answered some initial questions, I also must say
that it is truly incredible that you've taken out time to design & develop
the entire database. Moreover, your offering to answer all questions via
telephone leaves me simply stunned!!!

I really feel comfortable about the process now and know that I can modify
the sample database in order to meet all my needs. Seems like I only have
to press the "Import Button" and my job is done!!! SMILE!!!!

Again, thousand thanks to you!!!!

VR,
Tom

 

[tina]

you're very welcome!

Tina:

You have simply been awesome!!!

In case someone reads this...

I am working on a project that required some automated data imports. I had
no clue how to efficiently design a table structure and import routines.

And then Tina came along....

So, here's my reply to you:
Tina, not only have you answered some initial questions, I also must say
that it is truly incredible that you've taken out time to design & develop
the entire database. Moreover, your offering to answer all questions via
telephone leaves me simply stunned!!!

I really feel comfortable about the process now and know that I can modify
the sample database in order to meet all my needs. Seems like I only have
to press the "Import Button" and my job is done!!! SMILE!!!!

Again, thousand thanks to you!!!!

VR,
Tom