I have been working on a data system. the problem is that I have come unstuck. The acadimia seem to think that the system is too complex.

I have been vertually ordered to try again.

I shall include a screen shot of the troubled area and need help improving this data relationship.

BTW: this is a test version holding little more than the keys required.

It is difficult verging on impossible to give any feedback without understanding what this data represents, what the rules are etc.

One entity has many branches (shops ie Address)
each entity (say tesco) is in many categories
each category (CAT) may be nested within another n deep.
there are many questions that require answers
this questions appear in many categories (but not all)
Each address must give answers to the questions that the entity gets by being in a category.

Which bit don't "academia" like? I can imagine some misgivings about the category hierarchy, simply because hierarchies are difficult to work with in SQL. But your table diagram seems to correlate to the rules as you define them above: if the rules are correct, how could the tables be simplified?

I am told that my worries about linking up answers and questions properly shows that it is too complex.

And I have been asking -what about Questions and answers what relationship (if any) do I need to add?

Surely all you need to add is the relationship:

Question -(1)-------(many)- Answers

Ye...ees...

Would this not risk referential nonesence?

Can this be limited? If not by relationship then what SQL might I want.

I am worried that a weakness could exist within the data structure.

?Ideas?

I have related Question to answer as has been sugested.

I can get query to find all the questions "asked" and see the answer given (answer field) I can also create a list of all the questions that should be asked.

What I can not do is add the difference. That is I can not update the answers table with links (that become the KEY) based on what should be there.

help.

Ah, I think I see what you mean: an Entity should only answer Questions that are relevant to the Category of the Entity, right?

Some constraints are just too complex to be visible on an ERD, and this is one such. It is a multi-table check constraint or "assertion" that would be expressed via some SQL referencing several tables. Some DBMSs support such multi-table check constraints, but many don't. Alternatives are triggers or stored procedures.

This doesn't mean your design is wrong, it just means that the rules are indeed fairly complex.

Yes they are indeed complex.

The database is going to be an Access 2000 for the whole proto-type period so I fear stored procs are out.

The way I have been requesting the data means that irrelivant or unconnected answers are ignored.

I've added an updated image. The red line indicates the path of Query to select the correct questions and then find the answers. The black arrow shows the data path used to identify which question the answer is for.

In this way "irelivant answers" are ignored.

dispite this I can not (useing SQL) balance the books. I can identify all questions to be asked and the answers given so far but I can not add the records for the answers that are "missing"

Any SQL genius (what is the plural for that word?) about?

hhhhmmmm.... quite a situation here - between the theoretical answer, and the practical answer...

really hard to balance between the two...

????

let me try my hand on this

I've had a good play an spoken to Dr xxxxxxxxx at the local college. Aparently this guy is rather good but he has not come up with an answer yet. So I'm still as stuck as ever.



(names not given out over the internet)

select distinct q.question, a.answer
from question q, answer a, questioncat qc category c, catentity ce, address ad
where q.question_id = qc.question_id
and qc.catid = c.catid
and c.catname (???) = ce.cat
and ce.entity = ad.entityid
and a.address_id = ad.addressid
and q.question_id = a.question_id;

The above should give you all relevant questions that have been answered.

select distinct q.question, null
from question q, questioncat qc category c, catentity ce, address ad
where q.question_id = qc.question_id
and qc.catid = c.catid
and c.catname (???) = ce.cat
and ce.entity = ad.entityid
and a.address_id = ad.addressid
and q.question_id not in (select a.question_id from answer a);

This should give you all the relevant questions that have NOT been answered.

Union the two statements, and you should get what you want, or am I missing something here?

No you are likely not missing something. It is the unanswered that are so vital to me.

I can now test this and begin to develop that area (again)

thanks - I'll let you know how it panned out.