Normalizing an Information DB question (1 Viewer)

NotSoRandomOne

Registered User.
Local time
Today, 03:02
Joined
Sep 15, 2009
Messages
51
This is hopefully a quick question for those more knowledgeable than me. Say I am creating an information DB with 'Topics' and 'Subtopics'. A straightforward approach is one which has a Topics table, and another Subtopics table, with the subtopics being linked to the topics by their ID.

Can this be designed flexibly, so that a user can easily post information to a topic, rather than being forced to have a subtopic for everything? For instance, if I create a 'History' topic, instead of being forced to choose the subtopic 'Greek' or 'Roman' to apply an 'Information' item to, I can apply it directly to the 'History' main topic?

I can see doing it by eliminating the subtopics table, and just creating a 'History' item in the Topics table, and another 'History, Greek', etc. But that way means long dropdowns for all topics, and it also makes it difficult to query by master topics.

I can kinda see a way to do this using calculated tables to hold IDs for all Topic/Subtopic combinations, which I would have to update every time I added a topic or a subtopic, but that way QUICKLY becomes very complicated.

Is here something I'm overlooking that simplifies this type of design?

Thanks!
David
 

June7

AWF VIP
Local time
Today, 00:02
Joined
Mar 9, 2014
Messages
5,424
Can have fields for Topic and Subtopic in record and allow not choosing a Subtopic. But if goal is to categorize to Subtopic then why allow it to remain empty? If History is a Topic and there are not Subtopics associated then I guess that really means Subtopic is also History.

Topic/Subtopic is cascading data - choosing Topic limits Subtopic choices. Use a table of all Topic/Subtopic pairs to enable cascading comboboxes. Yes, new Topic or Subtopic requires new pair(s) in table.

Otherwise, must have both fields in record and user chooses to enter in both in any combination or just one.
 
Last edited:

NotSoRandomOne

Registered User.
Local time
Today, 03:02
Joined
Sep 15, 2009
Messages
51
Thanks. I will think about this more. Maybe it will be most flexible to record information against the ID of the Topic/Subtopic combination table.
 

vba_php

Forum Troll
Local time
Today, 03:02
Joined
Oct 6, 2019
Messages
2,884
NSRO,

If you're still reading this thread, it seems like all the big boys do it the traditional way. I will never be a fan of MS, but I believe all of their forums require very detailed information so they can re-direct the question asker or inquirer to the right person, whoever that is within their million or so contractors they've got involved with the process:

https://drive.google.com/open?id=1zIkUi0LBLGVMeBkdm10EnTWO0tfkuR2-
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 28, 2001
Messages
27,001
NotSoRandomOne,

I understand your question. The only way I can think of doing this is fairly simple but might require a little (ONLY a little) extra setup. Make the default at each level always keyword "General" (meaning "no specific topic" or "no specific subtopic"). Then if you don't select a topic, it is listed under general. That means you would only select topic or subtopic or subsubtopic if they apply. You can search for all "History" entries with

Code:
"SELECT Information FROM InfoTable WHERE LEVEL1 = 'History' ;"

But if you wanted to drill down specifically, maybe you would ask for

Code:
SELECT Information FROM InfoTable WHERE LEVEL1 = 'History' AND LEVEL2 = 'Greek' ;

The first query would return everything about History including Greek History and General History. The second query would only return everything about Greek History.

If I did not misread your question, this would be what you are trying to do, yes?
 

NotSoRandomOne

Registered User.
Local time
Today, 03:02
Joined
Sep 15, 2009
Messages
51
Thanks for the replies - I got it working with a Topics table, a Subtopics table, and a TopicsAndSubtopics table, which holds a list of the active combinations, against which the information is recorded. It doesn't require a subtopic selection, so if that field is null the information shows as being recorded to the main topic.

The_Doc_Man: Yes, that is one way to go. Thanks for the input!

David
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 28, 2001
Messages
27,001
so if that field is null the information shows as being recorded to the main topic.

If you truly meant "null" as the Access value NULL then remember that you cannot compare anything to NULL and get it to match - even if comparing NULL to NULL. Which is why I suggested having a default value that isn't NULL.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:02
Joined
Feb 19, 2002
Messages
42,981
The third table is not necessary and is probably wrong. The subtopics table should contain the foreign key of the topic it relates to. You seem to have made the mistake of making subtopic unaffiliated with a specific topic therefore requiring the third table. This is not a m-m relationship between topic and subtopic, it is a 1-m relationship. Just because you have two topics that each have a subtopic named "Greek" doesn't mean that you want to mush the items into the same group. When you are looking at topic1/Greek, it will make no sense if you also see topic2/Greek items.
 

Users who are viewing this thread

Top Bottom