Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Better Relational design

    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.
    Attached Thumbnails Attached Thumbnails relationship.jpg  
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Better Relational design

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

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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?

  5. #5
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Surely all you need to add is the relationship:

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

  7. #7
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  8. #8
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  10. #10
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  11. #11
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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?
    Attached Thumbnails Attached Thumbnails updated.jpg  
    Last edited by Matt_T_hat; 03-18-04 at 10:01.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  12. #12
    Join Date
    Dec 2003
    Location
    Tokyo Japan
    Posts
    26
    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

  13. #13
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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)
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  14. #14
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    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?

  15. #15
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •