Database Design

[JKarchner]

I am creating a database for a friend that will keep track of issues and
questions. I think it should be set-up similar to a family tree because
there should be a main question and then the ability to have follow-up
questions to those main questions. Of course those follow-ups could also
have follow-ups. After talking with my friend, she assured me that there
would be at most 5 levels deep. My current database design has tblTypes (the
different types of documents), tblSections (the different sections in the
documents), tblVersion (the versions of the documents), tblQuestions (uses
the previous tables to say where the question comes from and what the
question is), and tblAnswers (the answers to the questions, each question can
have multiple answers). Thank you for any help you can provide.

 

[Fred]

I noticed that nobody answered.

Your question mentioned a lot of different things including many different
terms which have meanings only to you, without defining them or describing
any real world connection between them. So you didn't really define the
mission nor the information. There's not even enough there to ask questions
about.

You defined your mission in terms of issues and question, but didn't say how
they relate to each other.

You posted because your current database design isn't doing the job, yet you
spent half of your post describing your database rather than exactly what
you are trying to do. And you introduce "documents" and "sections of
documents" without saying what they have to do with your mission which you
stated in terms of "questions" and "issues"

I wrote this to be helpful, and hopefully it didn't sound nasty.

Sincerely,

Fred

 

[JKarchner]

Sorry I thought I did a good job of explaining what I had already and what I
had wanted to do with it. I have come up with a different way to go about
doing what i wanted, but i guess now i want a different opinion if there is
one. I will do my best to explain in as simplistic manner as possible. The
first thing i would like to reiterate was that i thought it should be done
like a family tree or a business organization tree. With "parents" linking
to "children". I presonally like the business organization tree better,
because it is more similar to what I wanted to do. I would have a
Question/Issue that could have a follow-up Question/Issue, much the same way
that an employee would have a Manager. A question could only be a follow-up
to one question, but a question could have multiple follow-ups (1-to-many
relationship).

I have seen the way that family trees are done with a relationship table,
i.e. personA is related to personB because personB is personA's father. I
thought that creating a table for relationships would be useless, since
questions are only parents of other questions. I thought a business
organization would be better because I could take the approach that TABLE_EMP
has an EMP_ID and a MANAGER_ID where MANAGER_ID links to the EMP_ID to create
a recursive link (i think that is what it is called).

Please disregard the comments about the Sections, Documents, and Versions,
they are just ways to better describe the details of the question, much the
same way that Department and Position better to describe the Employee.

I guess that by writing out exactly what i wanted I kind of answered my own
question as to how it should be laid out. If that is correct thinking. My
questions would be, could i have a table TABLE_EMP with three fields: EMP_ID,
EMP_NAME and MANAGER_ID where MANAGER_ID links to TABLE_EMP to display the
Manager's Name? And is there a way to print out the organization tree, i.e,
print out the Manager and all the people that work for him and then all of
the people that work for them? Thanks!!!

 

[Jamie Collins]

I have seen the way that family trees are done with a relationship table

That's just one approach, called the adjacency list model; it is often
posted in unnormalized form. Much has been written about trees in SQL
e.g.

Graphs, Trees, and Hierarchies
http://www.dbazine.com/ofinterest/oi-articles/celko24

There's even a book on the subject (Joe Celko's Trees and Hierarchies
in SQL).

Try doing a google search for an example of modelling newsgroup
discussion threads in SQL (IIR it's quite a common one).

Jamie.

--