Multiple Tables - How to Approach? (1 Viewer)

[jr007]

Hi all

I looked to make sure I shouldn't be posting this elsewhere, and this seems to be the right place, I think.

I can't find anywhere that really puts a direct answer to my question, though it may be somewhat down to my lack of knowledge. Here is my situation:

I am creating a system that will use Access for its database. The system will be used to send communications to other teams, and will provide them with:

• Who they wish to send a communication to (team, individual etc.)
• Based on their initial choice, what type of query they wish to submit (e.g. if 'Team' were selected: feedback, complaint etc.)
• Based on the previous choice, more specific options. So, if complaint were chosen, the user will then see a list of choices specific to complaints (e.g. service, departmental etc.)

In the background, I refer to each of these "levels" as "level1" (first bullet-point), "level2" and "level3".

As the user will only ever choose from the options they are presented with (as opposed to free-type each time), how the hell do I go about creating tables for this?

[Added in edit to add detail:] With these level1, 2, 3 choices being prescribed (they must choose what they are presented), when it comes to uploading this data to the Access database, should they have their own tables (level1, 2, 3) or should there be a table for every potential list that can come up based on a user's choices along the way?

This is all in an effort to create an efficient database, and keep it 'normalized'.

It's worth stating that the database also collects unique data such as relevant order numbers, account numbers etc., all of which is sitting in its own table at the moment. However, from a reporting perspective, I would like to be able to view everything, including what choices they selected along the way, as well as the unique info mentioned.

The chances are, I'm missing something conceptual, but I could do with a steer even if that's the case.

Thanks in advance.

Jon

 

[informer]

Hi jr007,

Could you upload your physical data model (PDM) or better still the conceptual data model (CDM)?

 

[jr007]

Hi Informer

I just typed about a complete response only for my work network to crash and lose it!

I'll be honest, I've never done a CDM or PDM. I am a person who has used Excel, which has served me completely until now, but that does mean I'm a bit of a newbie when it comes to databases.

Having said that, what I currently have is very skeletal:

• 1 table with fields for what will be unique data
• 3 other tables (each named "Level1", "Level2", "Level3" that simply contains the lists that the application also presents them with.

If I want the ability to display a full set of information (unique data + the choices from each of these three lists that the tables also contain) am I best just adding three new fields to my main table (with unique data) and linking those fields to the primary keys of the 3 separate tables?

I don't mean to be rude in avoiding your request Informer, but it really is just the concept I'm trying to get in my head as opposed to a working solution, at this point.

Help appreciated so far.

Jon

 

[Minty]

From your description you sound as if you have grasped the idea of normalisation pretty well. I would draw out on paper your table ideas as you have them in your head and see if you only need one join from each table to one other table.
If they do you possibly are on the right track. Try and avoid Excel "horizontal" data structures, Access works best with "vertically" stored data.
Even if you miss out the detail fields the core data should sit in only a few tables with ancillary data stored around the base records.

Paste up a picture of your table / relationship once you have a initial plan.

 

[jr007]

Hi Minty

Thank you for the reply.

I have taken some screenshots of each table, and the relationships window to show a blank canvas.

Just so you're aware: the tables in the screenshots only contain data focussed on if a user - when using the application containing lists just like those shown in level1,2,3 - had gone down the route of 'Complaint' 'Team Complaint'. The reason for this is so I don't design massive amounts of tables that are potentially unnecessary.

The question remains the same though: assuming the user selected 'Complaint', 'Team Complaint' 'Team Lateness', how does that data tie in to the table shown (tblFormData) that contains the unique info.

Thanks for your help!

Jon

 

[Minty]

Okay - as is often the case a picture paints a thousand words.
I don't think you want 3 separate tables for your combo's. I think you only need one with all your data in it, with the level stored as a field. You can then control which records are displayed by storing a parent value to refer back to the ID above. Top level complaint items would have a value of 0 for the parent
tblComplaintItems
ComplaintID
ComplaintLevel
ComplaintText
ComplaintParentID

Doing it this way you can add as many levels of depth as you need without having to add another table and with minimal disruption to your forms.

You only need store the actual ComplaintID in your complaint details table along with who when and the text they enter.

 

[jr007]

Ok Minty...

I kind of get what you're saying. I'm getting into using 'Paint' today so I've attached another screenshot.

I don't quite understand the parent side of things (looking now!) and how this will tie into my existing data, albeit your mentioning of a foreign key for 'ComplaintID'?

In case it matters, the application submitting data to this database is Excel-based (built a good while ago) and uses either buttons or listboxes that are categorises as level1, 2, 3. In the case of the listboxes, they will have data that match this table perfectly.

Thanks for all of your help so far!

 

[Minty]

If you are only storing the data and not entering it in a form then the parent thing is a bit moot to be honest.
The parent id thought was to store the next level up value and only display the relevant child records based on that "parent" records in the complaint items table so your cascading style combo box would have an easily linked set of items.

 

[jr007]

Understood...

Is what I'm doing breaking some known rule of database use?

Is it the case that I have to succumb to including the level1, 2,3 data in the same table when each new submission is made?

Naturally, I'll end up with duplicates because the choices are limited, but all will be utilised multiple times at some point. So then, if I do wish to 'normalise' my database, is the only option to create separate tables for each level, add foreign keys for (level1ID, level2ID, level3ID) in my main table, and programmatically submit the level(1/2/3)ID number that is the primary key in each of these tables?

I now remember why I've avoided databases for so long, though I must persevere!

 

[Minty]

No you have everything you need in the tblComplaintItems, it is normalised. Creating tables for each level would NOT be normalised as it would mean a complete redesign should you need to add a further level.

 

[The_Doc_Man]

May I suggest that you search this form for "Cascading Combo Boxes" which addresses the idea that your 1st selection controls your 2nd selection and the 2nd selection controls the next level of refinement. This might represent some good reading and the discussions of how to set up the controls might let you see more about how to set up the tables that this situation would drive.

 

[jr007]

Thank you Minty. The_Doc_Man.

Minty, based on what we've discussed so far, and taking into account what you stated about the parent solution being a moot point if my database is only acting as storage (which it is), is this, in your opinion, the best path to go down?

This isn't me shifting liability to you! I respect others' knowledge and am wide open for advice.

The_Doc_Man, I'm not using any combo boxes. My database is merely for storage, though the data it will contain is absolutely hierarchical. The database is also external to the application the user will use to submit data to the database.

My effort is just to avoid the simple solution of throwing one record into one table each time a submission is made, which will create duplicate data within a very short period of time.

Thanks again for everybody's help so far.

 

[jr007]

FYI anyone who's interested!

I've made a disgusting looking mock-up of how the application will submit data to my troublesome database will work in an effort to support what I've written so far about it.

The terrible drawing alludes to the fact that:

1. Users can choose one of the three buttons (the caption of which will be inserted into the database, somehow)
2. Users chooses another button from those displayed. These buttons are dependent on the first button choice.
3. Based on the previous button choice, a listbox comes up offering specific subjects the communication relates to.
4. Users are then asked to enter a number of fields of unique data (goes into its own table in the database).
5. The data is submitted.

This doesn't use combo boxes; the application is NOT within Access; the database serves to store data only.

Thanks

Jon