Results 1 to 9 of 9

Thread: Impossible?

  1. #1
    Join Date
    Sep 2013
    Posts
    5

    Unhappy Unanswered: Impossible?

    I have very limited sql skills, but I have had a half-working project for 2-3 years, and I'm wondering if it's an impossible problem.

    First, here are the pertinent data points:
    Clinic name, address, state, county, services provided (usually several services per provider)

    Here is what I need to happen:
    1. User selects a state from a list populated from the db.
    2. User selects a county from a list populated from db based on selection #1.
    3. User selects a service from a list of available services in that state/county (based off their choices from #1 and #2, as populated from db)
    4. User clicks 'select', and a list of clinics that match 1, 2, & 3 appear with their address, etc.
    5. The db should be easily added to, via a user-filled form


    At any given time, I have been able to make 4 of the 5 work, but never all 5. Most of the time, I can get all but #3. I tried a new design, which works better, but still no #3. If I use a many-to-many design with 3 tables and JOINs, I don't think #5 is plausible.

    Anyone who can show me how this could be done will qualify for a pat on the back and $5.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What client/web tools do you envision using to implement the user interface? This does not sound hard to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2013
    Posts
    5
    Really? You think it's possible to get all 5?

    My thought was a password-protected form (php maybe?). Beyond that, I'm not sure how to answer your question. Any direction you could supply, however, would be appreciated! I'll look up what I need to!

    Thanks again!

  4. #4
    Join Date
    Apr 2011
    Posts
    34
    I agree with Pat. It does not appear to be that complicated. How did you manage to accomplish 4 out of the 5 staps? i.e. provide some specifics of the scripting language (PHP?) you used and where did you fail in making all the 5 steps you want to accomplish. You need to provide more details. Why did you bring up the option of a a password protected form?

  5. #5
    Join Date
    Sep 2013
    Posts
    5
    The password-protected form was about an eventual option for the user to add an entry to the database. I haven't done that yet, as I have 13,000+ clinics and 34,000 services in the database as is and I can't get it to work correctly. When I figure that out, I'll work on the form. The only reason I worry about it at all now is because I want to design the database layout such that it won't be too difficult to add entries in the future.

    So my original database was designed like this:
    Table 1: Clinics
    ID | Name | Address | Phone | Provider ID
    *Note: One clinic per line, one Provider ID per clinic

    Table 2: Services
    ID | Provider ID | Service
    *Note: If a clinic offer 5 services, there will be 5 lines with the same Provider ID, each with a different service, e.g.:
    1 100 Audiology
    2 100 Pediatrics
    3 100 Family Medicine
    4 200 Audiology

    Make sense?

    The third table (Counties) is just a table matching US counties with their respective state.

    This system worked well for filling the first box (State) via sql query.
    It also worked well for filling the second box (County) via:
    Code:
    $sql = "SELECT County FROM Counties WHERE State='".$state."'";
    I've had issues with filling the third box (distinct services in that county/state).

    I also worry that it would be difficult to design a system whereby a user-added clinic could be automatically added to that type of setup.


    That said, over the past few days, I've tried with a different setup, using a single table:
    ID | Name | Address | Phone | PID | Audiology | Dentistry | Family Medicine (etc)
    *Note: Each line in the table is a single clinic, with a "1" or "0" in each service column, denoting the presence or absence of that service in the clinic.

    The problem I've run into with this setup is that, while simpler and perhaps easier to add to, I can't figure out how to fill the third "Services" dropdown (based on the State/County combo from the other two boxes).


    So that's where I'm at. After I figure out how to best design the database and sql queries, I'll work on the input form. I wish I was better at this-- this has been in the works for 2-3 years now, and there are a lot of underserved populations who will benefit from this (it's the nation's largest database of low-cost medical services). I'm trying to get it running before an article runs on it in a national public health journal soon.

    Again, thank you all for your help! Any advice or direction will help not only me, but thousands.

  6. #6
    Join Date
    Apr 2011
    Posts
    34
    In your design, the clinics through the address or better a specific field must be assigned a county. Now since the clinic is in a county and the clinic offers a given service, you know which service is offered in a given county.

    Also if you are not familiar with many to many relationship, do some research. A clinic can have many services and a service can be offered by many clinics. So you need your clinic table, a service table and a joining table maybe called service_offered that would contain at least the clinicID and the serviceID

  7. #7
    Join Date
    Sep 2013
    Posts
    5
    My problem is that each clinic offers multiple services. I'm somewhat familiar with a many to many relationship- that's how it's been set up (with a join table) for the past 2+ years. My problems with this approach have been:
    1) Populating the third dropdown box with all the services that are offered for a specific state/county, and
    2) The perceived difficulty of adding a new clinic via a form when a join table is included. It sounds pretty complex, if possible.

    That's what I'm struggling with- getting all 5 requirements. Any advice for a better db design or perhaps sql queries that could lighten the load a little?

  8. #8
    Join Date
    Apr 2011
    Posts
    34
    1) You have to familiarize yourself at building queries using JOIN statements MySQL :: MySQL 5.0 Reference Manual :: 13.2.8.2 JOIN Syntax. I have databases with dozen of tables and using JOIN statements is a must.
    2) If you don't know how to do your inserts/updates, learn how to do it from the MySQL prompt to test it.

  9. #9
    Join Date
    Sep 2013
    Posts
    5
    Thank you!

Tags for this Thread

Posting Permissions

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