locked
Need database design help RRS feed

  • Question

  • I am starting a new project but I am puzzled on how i would design a specific portion of my database, so I am looking for input.

    I have case management system and for each case I have has a different Element. An element is essentially a category on which data needs to be entered on. For each element there are results, findings and analysis. A case can have many "different" elements. Each element can only have 1 finding. Each element can have many different results.

    I have the bulk of the design down which is shown below. The problem I am running into is the findings and results all have common elements for each case which makes my design straightforward. The Analysis portion of each case is different for each element and I am confused on how I would store different data for each Element without creating a new table for each element Analysis, especially if elements could get added anytime with different Analysis fields. There is no commonality between any of the Analysis fields I need to collect.

    Here is what I have so far, abreviated..

    Case Table (CaseID (PK)

    Element Table (CaseID (FK), Element ID (PK), ElementTypeID (FK LOOKUP)..)

    Result Table (ResultID (PK), ElementID (FK), ResultTypeID (FK LOOKUP)...)

    Findings Table (FindingID (PK), ElementID (FK)...)

    Based on this design each element will have different analysis fields but only 1 analysis per element.






    • Edited by spark29er Wednesday, May 9, 2012 5:19 PM
    Wednesday, May 9, 2012 5:16 PM

Answers

  • A case can have multiple elements , so ;

    1. Case table

    2. Element Table 

    3. Case Element Association Table (FKs to both Case & Element Table) - Finding can be a column in this table, since each element for a given case has only 1 finding

    Elements have Results, Findings & Analysis , but each element can have only 1 finding , but there may be multiple results (Please provide more details on what kind of data you need to store for Analysis)

    4. Results table (FK to case element association table), because for every given combination of case & Element, you can have multiple finding ( This is my interpreation , if you requirement is different, please clarify and i can suggest alternatives)

    5. Can't really talk about analysis in much depth and you have not provided any detail, but i would be inclined to follow the same logic as i have mentioned for the results table.

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Thursday, May 10, 2012 6:41 PM

All replies

  • A case can have multiple elements , so ;

    1. Case table

    2. Element Table 

    3. Case Element Association Table (FKs to both Case & Element Table) - Finding can be a column in this table, since each element for a given case has only 1 finding

    Elements have Results, Findings & Analysis , but each element can have only 1 finding , but there may be multiple results (Please provide more details on what kind of data you need to store for Analysis)

    4. Results table (FK to case element association table), because for every given combination of case & Element, you can have multiple finding ( This is my interpreation , if you requirement is different, please clarify and i can suggest alternatives)

    5. Can't really talk about analysis in much depth and you have not provided any detail, but i would be inclined to follow the same logic as i have mentioned for the results table.

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Thursday, May 10, 2012 6:41 PM
  • each element will have an analysis portion consisting of different fields as mentioned above.

    So for example Element 1 that particular Analysis may have 4 or 5 fields of different value types (int, txt, varchar etc)

    Element 2 Analysis may have 10 different fields of different value types.

    Thursday, May 10, 2012 6:45 PM
  • Ok, that means 1 element with have 1 analysis - but the analysis can have varying number of attributes. So, you guessed right that you cannot have this stored as separate columns. If you are familar with XML, i would recommend having an column called analysis of XML data type to the structure i mentioned in item 5 in my last post. One of the best ways of storing such variable attribute data is in XML type columns. You will need some familiarization with XML queries to work with this effectively, but it is easily achievable. The only alternative to XML that i can think of (without breaking rules of normalization - but having that XML column is already breaking them to some extent) is to have one row per attribute in the analysis table. you would Foreign Key it back to case analysis association table.

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com


    Thursday, May 10, 2012 7:21 PM