Post reply

data architecture

  • March 31, 2004 at 9:58 am

    #82485

    Guys, I apologize in advance for this question. But any responses would be appreciated.

    I’m not a DBA and trying to optimize the data structure of a DB with around 50 tables and 750 fields. This is medical data.

     

    Table1 has AppointmentID (PK) and AilmentID (FK) columns with other columns relating to the Appointment. Table has around 40,000 records and maybe 10,000 of them have associated AilmentID.

     

    Table2 is a connection table: AppointmentID and PathologyID. One appointment might have several PathologyIDs associated with it.

     

    Table3 is PathologyID (PK) and associated columns.

     

    Most of the time I am interested in the connection of AilmentID to PathologyID which would involve two joins.

     

    Question is whether to make a connection table with just the AppointmentID and AilmentID. One AilmentID often has several appointmentIDs.

     

    This would not change the two joins needed for AilmentID to PathologyID, but would mean an extra join any time I needed to connect the Appointment table with an AilmentID.

     

    I get protests from FE developersonName>ersersonName> when I start breaking up the tables because of the extra joins. I realize that everyone has a different personName>ersersonName>pective on this, but I’m trying to weigh the pros and cons and come to a consistent standard across the DB. If I decide to break out AppointmentID and AilmentID into their own table, then to be consistent I would need to do that in around 10 other places across the DB. It would mean that most of the time instead of having a PK and several FKs in a table, I would have a PK only with the connections occurring through separate tables. This is not an issue of duplication of data, so it comes down to structural integrity. If the extra overhead incurred with extra joins merits something, then that is what I need to grasp (and don’t yet).

    Thanks so much.

     SMK

  • April 1, 2004 at 6:30 am

    #501423

    data structures should be sepperate, when any of your field data in a table is repeated.

    example like patient record

    is linked to more than one address, & treatments, & appointments, etc... so you create a 'one to many'; or 'many to many' relationship

    a very useful relationship is two keys in a single table used to join tables as in a 'many to many' relationship

    most of the time its 'one to many' like address has a key pointing back to patient, with another field like seq or date to maintain the order of occurance (so how many addresses can one patient have?) infinite

    joining them later is by using the keys which are indexed in each table for fast queries & quick joins

    Hope this helps!

    Regards,

    Coach James

  • April 1, 2004 at 7:10 am

    #501431

    I don't a need for you to have a separate table for AIlment and Appointment as you already have those 2 keys in the Appointment table.  That would be storing data for no real reason in my opinion.  You can easily get the data you are looking for witht hte current structure with this query:

    Select

    AL.AilmentId,

    P.PathologyId

    From

    appointments A Join

    ailments AL On

    A.AilmentId = AL.AilmentId Join

    AppointmentPathologies AP On

    A.AppointmentId = AP.AppointmentId Join

    Pathologies P On

    AP.PathologyId = P.PathologyId

    If the developers don't want to write joins create a View that does it for them.  Or create a datawarehouse associating the Ailments and Pathologies in one table.

     

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • April 1, 2004 at 8:01 am

    #501448

    Thanks, guys! My data is normalized, I am just trying to put the finishing touches on it so I don't have to make any more changes. I am trying to standardize some things for a whole lot of other people and looking for "best practices". So I am being picky about details and trying to grasp the "whys" for different approaches. DBAs vs FE developers have different needs and therefore different preferances. It's those different perspectives I am trying to dissect.

    But that said, I think I'm there and hoping to tie a bow around this structure!

    Thanks,

    SMK

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply