Fine Tunning Access Relationships: tables optimization

[src23]

I want to develop an Access database (which is supposed to hold about 10.000-20.000 records); right now I'm designing the tables and I'm facing a problem which gives me two options.
First, the database description: It will contain one 'wide' table - tblRequests - 48 fields, from which eight of them are foreign keys, which points to eight categories of persons. Those categories of persons have approx the same number of fields and field types.

The two options to implement these tables are:



Option A. I make one table for every category -> total = 8 tables. Then I link the eight tables to tblRequests.

Option B. (which is easier) - I will make only one table which will contain all the persons from every category; it will have ONE MORE FIELD, named 'Category' - showing the category of every person. Then I will link the ID of that table (tblPersons) to those eight foreign keys in tblRequests.


----------------------------------------------------------
Contra to Option B:
The reason for which I hesitate choosing Option B is that the fields necessary for every category is slightly different from one categ. to another, and there will be about 300-400 (out of 1500) records with 4-5 (or even six) fields empty => 'a little bit of DENORMALIZATION' (the persons from all the categories count together about 1500).

-----------------------------------------------------------
Pro to OptionB:
But there is a reason which makes me choose Option B

If I make a select query from tblRequests, including in the criteria several persons from several categories, then in which case the query will run faster: when I have eight normalised tables linked to tblRequests, or when I have ONLY ONE table linked to tblRequests ?
I don’t know in which of those two situations the SQL will work faster - (personally I think Option B – but I have no foundations) finally I think I will choose option B because it seems easier to implement

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

Any help (or links) is much appreciated.
Thank you in advance.

 

[willir]

BuggyCode - !Not

Okay, you may want to review some theory on Normalization...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Based on the limited info, you need to answer some basic questions...
Can a person belong to more than one category? Sounds like - yes.
Can a category be assigned to more than one person? Agains, sounds like - yes.
This suggests you have a many-to-many, M:M relationship.

Next, if you use "Option B", will you have many fields unused where not all categories are assigned? Again, I suspect - yes.

Here is the rub.
- Although it may seem easier to design one table with all categories for each person, you may end up real nasty SQL statments. Your SQL statement may have to check numerous fields on the table to determine the results.
- What will you do if you have to add a new category? Will you have to add another field to the design of the table ... and then modify forms, reports, queries to accommodate the design change?? Non-normalized designs can require much higher maintenance.

I am not sure if Option A is correct also. Are the categories so different that they require separate tables?

Here is a though, using the M:M relationship. (Remember, I am only guessing on your current names)

tblContact[/b[
ContactID - primary key
ContactLN - last name
ContactFN - first name

tblCategory
CategoryCode - primary key, text?? or numeric??
CategoryName

Discussion:
I am not sure if it makes sense to use a text code or numeric for the primary key. The advantage of using a text string is that you can sort data elsewhere without linking to the category table. Usually, as a personal preference, I would opt for the text code, espeically if there is not too many categories.

tblContactCatProfile
ContactID - foreign key to contact table
CategoryCode - foreign key to category table
RevisionDate - date field

Primary key = ContactID + CategoryCode

Discussion:
This table joins the contact table and category table. It is also called an intermediary table. A record is created for contact for every category they belong to. Using the Primary key of ContactID + CategoryCode ensures only one Contact + Category is created.

Here is a look at my (silly) test data...
[tt]
tblContact
ContactID ContactLN ContactFN
1 Atwood Margaret
2 Brooks Terry
3 Asimov Isaac

tblCategory
CategoryCode CategoryName
INTL Intelectual
FUN Fun
FANT Fantasy
SF Science Fiction
PHIL Philosophical
SERS Serious

tblContactCatProfile
ContactID CategoryName
1 INTL
1 PHIL
1 SERS
2 FUN
2 FANT
3 INTL
3 SF
3 PHIL
[/tt]

Does this make sense??
Richard

 

[src23]

First of all, I want to thank you for answering me,
and I apologise I couldn't answer sooner.

About your answer: yes, you are right, and your example DOES make sense and was meaningful and helpful to me.


About the tables:
FIRST - one more thing about the intermediary table that you wrote above:

tblContactCatProfile
ContactID - foreign key to contact table
CategoryCode - foreign key to category table
RevisionDate - date field

On the fields ContactID + CategoryCode I used to create an unique index, not a primary key. Is this right ? Or should I use PrimaryKey (which includes the unique index)
If yes - then why ?



SECOND - about the problem - I should have given more info about it.

First of all, a person belongs to ONLY ONE category. He can change his category, but that's very unlikely to happen.

Then, the number of categories - will not change:
In tblRequests I need to store data about 8 types of persons. It's very unlikely that number of persons will change (it's a standard: a request comes from another dept where it is filled-in by a client, saved by an office person, analysed by an analyst, must be signed by a manager, etc etc ... the problem is a little more complex).


But, for the moment, there is one thing I need to know:

Suppose this two situations :
A - tblRequests - and to this table are linked five other tables (Categ1, Categ2 etc, storing persons from every categ).
B - tblRequests - and to this table is linked only one table tblPersons, which contains all the records from all the categs.


Suppose that I want to select only those requests where persons from Categ1=XXX and Categ2=YYY and Categ3=ZZZ.

The question is: When will SQL run faster: in case A or B ?
I don't know how SQL QUERY works when it joins only two tables(tblRequests and tblPersons)Op. B, and when it joins all those five (or N) tables with tblRequests (Op. 1) ? Which one is recommended ?



What should I read about optimizing Relationships and Normalization together with DATA ACCESS SPEED ?


Thank you very much.

 

[lespaul]

anytime you start naming things:

Categ1, Categ2

you have not normalized.

what exactly are you trying to do? Track requests between departments and approvals of requests?

If your tables are normalized, optimization will happen, you don't need to do anything more. There's a thread around where someone did their thesis or grad paper or something on the speed of queries against normalized data vs. non normalized data, if I can find it I'll post a link.

On the fields ContactID + CategoryCode I used to create an unique index, not a primary key. Is this right ? Or should I use PrimaryKey (which includes the unique index)
If yes - then why ?

Yes you would make a composite Primary Key of ContactID and CategoryCode. This will by definition prevent each contact from having the same category code more than once.

ContactID CategoryCode
1 xxx
1 yyy
1 zzz

all the above are valid entries;

but I couldn't add another record with
1 xxx

because that represents a duplicate PK.



Leslie

 

[src23]

Hi.

Thanks for the tips. I've been considering your advice regarding Categ1, Categ2.... and you're right.
Usually, when I have to store different categories of things, I store them in a single table, like you said. But now the situation is a little different.

First of all - you mentioned something about

thesis or grad paper or something on the speed of queries against normalized data

I searched for that on the forum and I didn't find anything like this (maybe I searched in the wrong place). I'll be glad if you could give me a link to that.


I dont't want to bother you with stories, I have to make myself clear: analysing the problem, there are eight different categories of people who are involved in a request. I will describe the tables (number of fields and an approx number of records).
Note that I may break the rules of normalization, but I want to increase the speed of queries, because of the very large differences between the size of those tables

-----------------------------------------------------------
Categ. name Approx_num_of_records Num of fields
-----------------------------------------------------------
GeneralManager 2 10
InternalLoanOfficer 15 16
InternalLoanAnalyst 25 16
AgencyAnalyst 1200 8
AgencyOfficer 300 8
AgencyRiskManag 55 8


and, of course,
the clients: 10.000 28
which is a different category - I won't consider it in this post.



Rules: A person from a category mentioned above may appear on one or more requests. On a request there can be ONLY ONE person from every category. A person can not change it's category (at least for the moment, to simplify the problem).


I omitted unnecessary things to simplify the problem, without affecting the essential.


So let's say the Requests will contain, besides its fields, the foreign keys of the first six tables

My question is:
When will my database work faster:
A: if make six (or... maybe less...) tables to store every category of persons in its own table ?
B: or I store all those six categs in a single table and link it to six foreign keys in Requests


I'm interested to know, if you can help me, how SQL works with queries on many joined tables and/or queries...


The Requests will count about 2000-3000 records (FYI, I'm not trying to make an Access database for a bank to store loan requests - I'm not that fool - it's intended only for some statistics stuff about loan requests)



If you have enough patience to read my post, any help is much appreciated
Thank you in advance..