Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2007
    Posts
    13

    Exclamation Design Problem . . . ! ! !

    Hello all . . .

    I'm creating a database schema and i need some help.

    I have two tables

    The first table looks like

    Table1
    tb1_ID
    tb1_data1
    tbl1_data2
    tbl1_data3

    The second table looks like

    table2
    tb2_ID
    tb2_TB1_ID
    tb2_data1
    tb2_data2
    tb2_data...
    tb2_data25

    The tb2_TB1_ID is foreign key to table1

    Now the problem that i have is the following

    each of the tb2_data(column_number) must have one to three sub records in an other table.

    How can i design this stracture ? ? ?

    Thanks a lot . . . .

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start by splitting off the repeating data columns from table2

    create table table2data
    ( tb2_ID integer
    , tb2_TB1_ID integer
    , tb2_number tinyint
    , primary key ( tb2_ID, tb2_number )
    , tb2_data varchar(99)
    )

    so instead of 25 columns, you have 25 rows

    as for your "sub records" you will need to explain this more clearly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    so instead of 25 columns, you have 25 rows
    Coo - so mike_bike_kite finally caught your imagination then eh? EAVtastic

    Seriously though - are you sure this is a first normal form problem or could it be that meranios has chosen an unfortunate way to label his\ her attributes for the purposes of the question?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    huh? caught my imagination? what are you on about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Good Lord. He could start by dropping the table prefixes from his column names.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2007
    Posts
    13

    about the third table

    about tbX_ is not prefix i gave that names because my original names are more deficault to write. are very long names and too many columns.

    The third table must have the following stracture

    table3
    tb3_id
    tb3_data1
    tb3_data2
    tb3_data3

    in table3 i will inserting records for each column for each record of the table2.

    The ammount of records in table3 will be from 1 record to 3 records for each column/record in table2

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    merianos - please could you put your genuine field names down? Rudy and I are interpreting your names differently and he is inferring that you have a not met first normal form because of it. data1, data2, data3 etc implies repeating groups.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by merianos
    The third table must have the following stracture
    must???

    okay, then, good luck to you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    13
    Is realy very very deficault to read all together . . .

    My main problem is on table2 and table3

    tha table2 must storing information for 25 deferent thinks. all of that 25 columns in table2 are storing only boolean values.

    My client told me "For each boolean information in table2 i have to store one or two or three other informations."

    If i make a table like

    table2
    tb2_ID
    tb2_TB1_ID
    tb2_data1
    tb2_data1_extra_information1
    tb2_data1_extra_information2
    tb2_data1_extra_information3
    tb2_data2
    tb2_data2_extra_information1
    tb2_data2_extra_information2
    tb2_data2_extra_information3
    tb2_data...
    tb2_data..._extra_information1
    tb2_data..._extra_information2
    tb2_data..._extra_information3
    tb2_data25
    tb2_data25_extra_information1
    tb2_data25_extra_information2
    tb2_data25_extra_information3

    i think is too wrong.

    so the problem is the table2 and table3.

    How can i do that ? ? ?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    must???

    okay, then, good luck to you
    To be fair - merianos's first language is clearly not English.

    Sorry merianos - without the requested information I can't help you. I don't know what you are modelling since you are using generic names & numbers.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Whether that design is wrong depends upon the nature of the data you are storing. Presumably you want your data normalized, but from the generic table and column names you have given we can't tell whether your schema is normalized or not.
    What kind of data are your storing? What is the purpose of this database?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Aug 2007
    Posts
    13
    The database is for a doctor.

    The table2 storing history data.

    For example :

    patient_lactation
    patient_sneeze
    patient_itch

    the above columns are a sort example of my table2 columns. In total are 25 columns similar to this columns and all storing boolean values.

    Now for each column, if the value is true, then i want to store, at the most trhee diferent types of drugs (medicens) for each case seperatly.

    ie.
    patient_lactation = true

    drugs for this column
    drug_name_1
    drug_name_2
    drug_name_3

    This is what i want to make.

    And this is the realy table but in sort view.

    Thanks a lot

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    For a normalized schema, you will need a structure similar to this:
    Code:
    Patients
    	(PatientID,
    	PatientName,
    	...)
    
    Symptoms
    	(SymptomID,
    	Symptom,
    	Description,
    	...)
    
    Medications
    	(MedicationID,
    	Medication,
    	...)
    
    PatientSymptoms
    	(PatientSymptomID,
    	PatientID,
    	SymptomID,
    	...)
    
    PatientSymptomMedications
    	(PatientSymptomID,
    	MedicationID,
    	...)
    Note: I used surrogate keys in this example. Others (Rudy...) may advise you to use natural keys. The important thing is to understand how these table relationships give your database application maximum flexibility.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the only table of all those you've listed, blindman, which potentially has a useful natural key is Medications

    a quick google turns up a couple of US patents on the code, but no information on the code itself

    use a surrogate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2007
    Posts
    13
    This is a very small part of my database project.

    That i have to do is add one to three sub records for each column in table SYSTEMATIC_RACH_EPISODES.

    For example if

    SYRE_SKIN_PALM_ITCH = true

    then the doctor will deside if he will give drugs to the patient for that symptom. And if he deside to give drugs he can give to him the most three drugs. In SYSTEMATIC_RACH_EPISODES will adding records for each visit of the patient. And in any visit will giving other drugs to the patient for each column of the table, or he will stop to giving drugs to the patient for the some columns.

    Any idea on how to do that ? ? ?

    http://www.datacenterhellas.eu/070820071135.jpg
    Attached Thumbnails Attached Thumbnails 070820071135.jpg  
    Last edited by merianos; 08-08-07 at 14:48.

Posting Permissions

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