locked
design a database object that has multiple values for each attribute of the this object RRS feed

  • Question

  • Above screen shot is the design of the actual form that we will give to our patients to fill out to store the info they will provide at the time of their initial exam. I am being asked to design/model the database objects to store this information. I determined that basically the fields in the far left side of this image (like 'chief complaint', 'posterior etc) to be the attributes and will become columns in the table. The check boxes are the actual values for these tables. All columns can have multiple values (meaning for each column multiple check boxes can be checked). I have basically thought out two approaches (please keep in mind that there are more columns then I have showed above, i only decided to show part of the whole table I am trying to design to just show an example):

    1) Design one table that will have a column for each possible value for each attribute. 

    pros and cons (in my opinion):

    it will make selecting and saving data from this table for the screen we are designing easy and simple.

    The table will be a very wide table (with almost 200 columns in it) 

    Am I violating some very basic database design principles here by creating one large table that will cause issues down the road if there are changes to the screen/template? 

    option 2) turn all check boxes to code tables (basically these will become entities of their own)

    if I do that I will now have to create a correlated table for each attribute (posterior, anterior) and relate a row in main table to multiple rows in each correlated table for each column.

    This will result in multiple code tables and one correlated table for each column. (18 correlated tables so far) 

    if I follow this design then in order to show the full picture of initial exam I will have to select data from over 18 tables involved here, and forget about updating them. 

    I am leaning towards the first solution because it is simple,

    the second solution is complicated and also I believe I am violating the very basic principles of database design by turning columns/attributes (posterior, anterior etc) to entities by turning them into correlated tables whereas these are really attributes and should result in columns. I will have to join between 18 tables at the very least If I want to show the complete exam picture. 

    I hope I have pictured my dilemma here, I need to design this object properly and the above two approaches are the only one I can think off, I need help to make the decision and may be there is another side to all this that I am not even taking into consideration. I hope someone has done something like this before and I can discuss this with them.

    thanks. 

    Tuesday, June 25, 2013 2:31 PM

Answers

  • if I understand this correctly, r u suggesting that all possible values for different types of diagnosis (eg DiagnosisPosterior, DiagnosisXrays) should be stored in associated tables and then have a one to one relationship between patientExam and these associative tables.

    If your answer is yes:

    then basically I am creating bunch of tables with subset of columns instead of grouping all these columns in the main patientExam table. If my conclusion is correct then although having all columns in one main table patientExam will create a very wide table but then I would not have to join between almost over ten to 12 tables to get the whole picture.

    As for this data I agree we thought about storing it as comma separated values but it will cause us problems down the road. These diagnosis will not have more data (cost etc) with them. 

    Thursday, June 27, 2013 4:38 PM

All replies

  • The design depends on how you are going to use those fields.  Are the values just for display, or are you going to search for "Overbite"?

    If you are rarely going to search by the values and they are mainly display values, I would suggest 1 field for "Posterior" with a varchar(max) and just put in a comma delimited string of the values, or and XML field.

    If you are going to search by the values, you should do option 2.

    Tuesday, June 25, 2013 2:50 PM
    Answerer
  • PS.  I would not create 18 tables with a handful of rows and join them.  I would use a "Name" table and put all the values in that table.  Like:

    CREATE TABLE [dbo].[Diag](
    	[DiagID] [int] IDENTITY(1,1) NOT NULL,
    	[DiagType] [varchar](25) NOT NULL,
    	[DiagName] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Diag] PRIMARY KEY CLUSTERED 
    (
    	[DiagID] ASC
    )
    )
    INSERT INTO dbo.Diag VALUES
    ('Posterior','Class I'),
    ('Posterior','Class II'),
    ('Posterior','Class III'),
    ('Posterior','X-Bite'),
    ('Posterior','Crowded'),
    ('Posterior','Rotated'),
    ('Anterior','Overbite'),
    ('Anterior','Open Bite'),
    ('Anterior','Crowded')

    Tuesday, June 25, 2013 3:00 PM
    Answerer
  • tom: thank you very much for your answer, great answer, yes I did think about creating a name table and then adding all codes to this table. However I will still need to create a correlated table for each column (posterior, anterior etc) and then join this correlated table with my main table initialexam. This will result in over 18 table join if I want to show the complete picture. This is what I am trying to avoid and if I do that is it a big no no considering the proper database design principles? what if de-normalize and save all my data in a very wide table where a 'bit' datatype column represents each option available for each column? is that a big no, no? 
    Tuesday, June 25, 2013 3:50 PM
  • 18 table joins is not that many.  I have queries with over 200 joins.  I wouldn't worry about it.  You can create views to simplfy the queires.

    I would not use individual fields or bit maps.  You will be sorry in the long run.   What if you later want to add soemthing to one of the groups?

    Tuesday, June 25, 2013 3:58 PM
    Answerer
  • You are right, it will create somewhat an inflexible table design and in future we have to add something to this group we will have to change our table schema, having said that is this the only reason (flexibility) that we should use the correlated tables. 
    Tuesday, June 25, 2013 4:52 PM
  • This is a tough one. I would definitely not agree with the comma delimited list (you are capturing this data to use for some reason, probably billing, right?)

    My initial guess is the Diagnosis table that Tom suggested is a decent place to start, (and I would dissuade you from the bit columns too,) because you are going to have more data about each diagnosis (codes, costs, etc). I doubt the purpose of this app is simply to capture the data that makes up this form, but rather to automate some process in the future.

    If each set of attributes is complex (and similar), I would consider a subclass of:

    Diagnosis
    DiagnosisPosterior
    DiagnosisXrays
    DiagnosisEtc

    Then you have one associative table between diagnosis and patientAppointment, and a template for "extra" data you will need for the "special" cases


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, June 25, 2013 9:39 PM
  • if I understand this correctly, r u suggesting that all possible values for different types of diagnosis (eg DiagnosisPosterior, DiagnosisXrays) should be stored in associated tables and then have a one to one relationship between patientExam and these associative tables.

    If your answer is yes:

    then basically I am creating bunch of tables with subset of columns instead of grouping all these columns in the main patientExam table. If my conclusion is correct then although having all columns in one main table patientExam will create a very wide table but then I would not have to join between almost over ten to 12 tables to get the whole picture.

    As for this data I agree we thought about storing it as comma separated values but it will cause us problems down the road. These diagnosis will not have more data (cost etc) with them. 

    Thursday, June 27, 2013 4:38 PM