DB design thoughts

Question

DB design thoughts

Jay@Work

SSCrazy

Points: 2633
More actions
February 24, 2019 at 3:47 pm

#373912

This is more of a question around DB design as opposed to DML so hopefully I'm in the correct place.
I need to build something that is best thought of as a web based FAQ system. At the top level will be categories, within each will be questions which may drill deeper until ultimately you reach an answer. I have no desire to create web pages for every question group and answer hence the DB road. I'm trying to visualise how I might use a parent child approach to build this, passing query string values along the way in order to return to the DB for more data.
So using a pet shop as an example, here is how a customer may use the system.
Page 1: Categories
Cats. Dogs. Budgies. Fish (customer clicks on Dogs
Page 2. First lot of Dog questions
Feeding. Exercising. Grooming (customer clicks on Feeding)
Page 3. Second lot of Dog questions
What food. How much food. How often. (Customer clicks on How often)
Page 4 You should feed your dog twice a day (Customer walks away educated)
I don't envisage that each "question path" would be the same depth as the others meaning it could be 2, 3 or 4 clicks down until you ultimately get the answer.
So maybe the Dog category has an ID of 1 with no parent. The questions on Page 2 have an ID of 1.1 (with a parent of 1), questions on page 3 have an ID of 1.1.1 with a parent of 1.1 and perhaps the answer is also part of the parent/child chain. Obviously I'm spitballing at this point.
If anyone knows of an article/template or words of wisdom to help me visualise this design that would be great!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

Jay@Work - Sunday, February 24, 2019 3:47 PM
This is more of a question around DB design as opposed to DML so hopefully I'm in the correct place.
I need to build something that is best thought of as a web based FAQ system. At the top level will be categories, within each will be questions which may drill deeper until ultimately you reach an answer. I have no desire to create web pages for every question group and answer hence the DB road. I'm trying to visualise how I might use a parent child approach to build this, passing query string values along the way in order to return to the DB for more data.
So using a pet shop as an example, here is how a customer may use the system.
Page 1: Categories
Cats. Dogs. Budgies. Fish (customer clicks on Dogs
Page 2. First lot of Dog questions
Feeding. Exercising. Grooming (customer clicks on Feeding)
Page 3. Second lot of Dog questions
What food. How much food. How often. (Customer clicks on How often)
Page 4 You should feed your dog twice a day (Customer walks away educated)
I don't envisage that each "question path" would be the same depth as the others meaning it could be 2, 3 or 4 clicks down until you ultimately get the answer.
So maybe the Dog category has an ID of 1 with no parent. The questions on Page 2 have an ID of 1.1 (with a parent of 1), questions on page 3 have an ID of 1.1.1 with a parent of 1.1 and perhaps the answer is also part of the parent/child chain. Obviously I'm spitballing at this point.
If anyone knows of an article/template or words of wisdom to help me visualise this design that would be great!

I'm thinking that you're spot on with the idea of a parent/child table (also known as an "Adjacency List") to drive it all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)