By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Design Problems With Too Many Junction Table, Please Help !

P: 91
Hello,

I am having problems with the design of the database and being new to Access, I am wondering if I am doing things correctly.

I have the following tables:

1. [Rules], Fields - RuleID, RuleName
2. [OwnerL1], Fields - OwnerL1ID, OwnerL1Name (name of a person)
3. [OwnerL2], Fields - OwnerL2ID, OwnerL2Name (name of a person)
4. [Location], Fields - LocationID, LocationName
5. [Function], Fields - FunctionID, FunctionName

The relationship between the tables are:

a. [Rules] have many to many relationship with [OwnerL1], [OwnerL2], [Location] and [Function]
b. [OwnerL1] and [OwnerL2] have many to many relationship.
c. [Function] has many to many relationship with [OwnerL1]
d. [Location] has one to many relationship with [OwnerL1]

The [Rule] table holds the different regulatory rules. These rules impacts many [Function] or business areas.

Each rule will have an operational owner at L1 level and there also will be an accountable person who is at L2 level. L2 level is higher than the L1 level. In many cases L1 and L2 owners are same.

Each [OwnerL1] is located in different business location [Location]. We are not bothered with the location for L2 owners.

THe purpose of the DB is to link the rules with different owners, locations and functions. The DB should be able to create the desired reports which can be shared with Function heads or L1 / L2 owners.

Eg, to create a report for all the Rules for an L1 Owner which will also show the other details.

With the above scenario, I have 6 junction tables. Could you please advise re the design of the database for the above scenarios? I am wondering if I am complicating things and if there could be a better solution.

I am attaching the design in a word document for your reference. I will be very grateful if you could share your opinion regarding the design.
If you need any clarification, please let me know.

Thanks,
SG
Attached Files
File Type: doc Database.doc (29.5 KB, 221 views)
Aug 13 '12 #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,282
sg2808,

Initially, it looks like your Rules Table should have a few more fields: OwnerL1 and OwnerL2, because, based on your descriptioin, each rule has just ONE L1 and ONE L2 owner. However, since the L! and L2 Owner MAY be the same, you may want to consolidate all Owners into one table, and the L1 and L2 fields of Rules Table looks to the same table for their data.

If each owner is only in ONE location, then there should be a separate location table, and the Owners table looks to the Location table for the location of each Owner.

If several different rules can all apply to several different functions at the same time, then I would have the Functions Table just like you describe. Then a separate table with FunctionRuleID, RuleID and FunctionID as the only fields, because this shows the relationship between.

Not sure you really need any other junction tables....
Aug 13 '12 #2

P: 91
Sorry for any confusion.
Each rule may have one or more L1 and L2 owner. The L1 and L2 owner details are in table [OwnerL1] and [OwnerL2] table.
Let me know if this changes your advice.
Aug 13 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
Welcome to Bytes.
Well done with the information you posted about your project.

Tweenyfo has provide a good start on critiquing your database so I'll offer my standard boilerplate of links.

Of the links below, you should start with the "Tutotial for Access" I have found that most text books and other references do (IMHO) a very poor job helping to set up a database; however, this site does a really good job.

--Z's BOILER PLATE -- just a few links I tend to post quite often:

I think you did well with the post... the following three are for future reading... esp. before you start posting code or SQL information
This is a must read: Posting Guidelines

How to ask good questions

FAQ

>>> Start Here>>> A Tutorial for Access

>>> Come Back To This next! >>> Database Normalization and Table Structures.
Aug 13 '12 #4

twinnyfo
Expert Mod 2.5K+
P: 3,282
I think you could still consolidate all owners into one table. And yes, then you would want to junction Rules with Owners (but twice). This basic structure should get you started....
Aug 13 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.