Table relations with fixed records and form design assistance

[Jerry Maiapu]

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!!!

[Rabbit]

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?

[NeoPa]

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.

[Jerry Maiapu]

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..

[NeoPa]

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.

[Jerry Maiapu]

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

[NeoPa]

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.

[Jerry Maiapu]

Ok thanks NeoPa. Would appreciate if someone else can also assist where necessary.

[nico5038]

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)

[Jerry Maiapu]

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

[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)