469,086 Members | 1,216 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Table relations with fixed records and form design assistance

Jerry Maiapu
259 100+
Dear all,
I have a simple but very important database fundamental question on table relations with fixed values and its form design problem I am facing for easy data entry.
I have about 3 tables
o tbl_company
o tbl_Main_category
o tbl_Sub_category

The Main Category table has 8 records and subcategory table has 100 records shared differently by the 8 records from the Main Category table)-one to many related

o These values from both table are definite field values and cannot change i.e. - no new entry is required. However the information that will change is the records in tbl_company table i.e. new company details will be entered and a corresponding score value will be entered in the sub tbl_Sub_category

o A company will have a category with its subcategory and a score assigned to the subcategory of each company..in which a sub category is related to the main category

Database performance/process expectations:


Behavior: When a new company is entered user need not have to re-enter any category + sub category infor to the new company since its preĖentered once
Form Design: Need to filter by company to see the related main and sub category values for each company.

My initial design:
First attempt:
2 Relationships:
o tbl_Main_category - one side - tbl_Sub_category -many side
o tbl_Main_category is the one side & tbl_company is the many side


Second attempt:
2 Relationships:
o tbl_company -one side tbl_Main_category - many side
o tbl_Main_category - one side - tbl_Sub_category -many side

Both the above table relations require the user to re-enter category & sub category values for each new company or vice versa.
I donít know if this is the best way forward. .i.e. re-entering same values for each company.

Note: Once a new company is entered a score for each subcategory is entered.
Would appreciate a way forward on this:

I have gone in circles trying to come up with a workable method with the table relations and now numbed and exhausted.

I am also thinking of creating 8 tables representing the 8 main categories and have the subcategories as the field values but that will violet database design rules.
I donít know how far I can apply the normalization rules.

Thanks in advance for any help!!!
Jan 15 '13 #1

✓ answered by nico5038

When maintainng this table, it's essential to keep it up-to-date with the main/sub category table.
As tables can change (even when you say it's not possible), I prefer to create these records always from scratch, thus making sure it's 100% equal to the original table.

Nic;o)

10 1573
Rabbit
12,516 Expert Mod 8TB
I'm having trouble understanding the relationships between the table, especially in regards to the score and why it would be stored in the subcategory table.

Can you post some sample data from each table?
Jan 15 '13 #2
NeoPa
32,159 Expert Mod 16PB
Jerry:
When a new company is entered user need not have to re-enter any category + sub category info to the new company since its pre-entered once
Surely every new company needs the FK values set up for the other two tables? I assume you are saying that that work will be the extent of what is required to categorise any new company?

I would expect that to require ComboBox lists populated with the existing records, from which one is selected for each of the Category and Sub-Category values.
Jan 16 '13 #3
Jerry Maiapu
259 100+
Rabbit, the subcategories are definite criteria names that are used to measure a company..once measured a score value is assigned to each sub category for the company..The subcategory names does not change for any new company as these are the measurement tools for company assessment. So like I was saying when a new company is measured, only the score value is required to be entered for each corresponding subcategory.

NeoPa You are right. That is exactly what I would like achieve in the table relations but could you eloborate further on populating values from combo box .

I am sorry if am too shallow on my question..
Jan 16 '13 #4
NeoPa
32,159 Expert Mod 16PB
I'm happy to explain further when I'm sure I understand what you're asking Jerry. I'm fairly certain you're talking about linking to separate tables using Foreign Keys, but it would be easier to be sure if you posted some example Category and Sub-Category data.

Assuming I'm right about what I think you're saying then :
To identify the Category (We'll ignore the subs for now as they're the same anyway.) from within the Company table you would want an FK field that would match one of the values in the PK of the Category records. To make this easy to enter you would put in a ComboBox on the Company form which is bound to the FK field, but contains a list of all the valid PKs from the Category table. Thus, only valid IDs are able to be entered. The ComboBox can include more than one field from the Category table in order to help the operator identify the desired record correctly, but only one field is actually stored in the Company's FK field.
Jan 16 '13 #5
Jerry Maiapu
259 100+
NeoPa,
I just did what you mentioned with some additions/changes.
I have moved the score field to a new table since each sub category in a company will have a different score.
Now on the company form I did the cmbo FK loop up and is fine (I think so)
Now since the sub category for the company is required to be also viewed on the same form I have created a subform filtred by Category FK on main form (company form) to Category FK on the subform (subcategory form-datasheet).

I am still muddled on how data is being handled (background relations) especially when entering a new company name since each company belong to a category having subcategories with different scores.
I don know if my table relations is correct.

I have attached the sample db for you & others (Rabbit) sighting
Attached Files
File Type: zip Measurement Report.zip (39.0 KB, 61 views)
Jan 18 '13 #6
NeoPa
32,159 Expert Mod 16PB
I'll look if I get some time Jerry, but these things normally take more than working on a page in text, and I'm very busy with work ATM (which is why I'm still up at 05:00 anyway). I'll leave it as an open page in my browser for now.
Jan 18 '13 #7
Jerry Maiapu
259 100+
Ok thanks NeoPa. Would appreciate if someone else can also assist where necessary.
Jan 19 '13 #8
nico5038
3,079 Expert 2GB
I get the impression you need to record the sub-category scores for a company based on the Main category that's defined when the company is defined.
Two remarks:
1) Not being able to change the Main-Category on a defined company is strange, as people do make mistakes.
2) Your Score table doesn't hold the Company-ID.

My approach would be to have the company-ID in the tblScore and to automatically add the subcategory records to the tblScore when the Main-Category is selected.
To make changes of the MainCategory possible I would just add a DELETE * from tblScore where Company-ID = current-Company-ID before I add the sub-category score records.

Getting the idea ?

Nic;o)
Jan 19 '13 #9
Jerry Maiapu
259 100+
Hi Nico..thanks for the reply. I agree with you on the table relations to have the tblscore connected to tblcompany but could yo eloborate further on data deletion portion of your suggestion...
Thanks..

Reply posted via android phone
Jan 22 '13 #10
nico5038
3,079 Expert 2GB
When maintainng this table, it's essential to keep it up-to-date with the main/sub category table.
As tables can change (even when you say it's not possible), I prefer to create these records always from scratch, thus making sure it's 100% equal to the original table.

Nic;o)
Jan 26 '13 #11

Post your reply

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

Similar topics

25 posts views Thread by Michael Schuerig | last post: by
reply views Thread by Nikolay Petrov | last post: by
4 posts views Thread by yanjie.ma | last post: by
3 posts views Thread by patl | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.