SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    San Francisco
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help! How would you design a schema around this data?

    I'm taking over a project from another developer, and one of the components is a "contacts database" which tracks work and home information on people working city service jobs.

    Right now, there are separate tables for police, firefighter, EMTs, coroners, DA's, and the like -- but they all track the same information (first name, last name, work address, phone, email, et cetera). There is a disparate number of each type of person: around 2,000 cops, 500 firefighters, 75 DA's, etc.

    Originally, I thought it would be best to combine all of these tables into one, and sort their types based on foreign keys.

    While they all track the same information, when I do need to display a list of some district attorneys, for instance, in means trolling through those 2,000 cop records as well. I added indexes on some of the keys to speed this up, but I'm concerned it's just bad design and will hamper me down the road.

    Do I leave it broken out into separate tables or combine them into one? Many thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    leaving it broken out or combining them into one is not the same decision when the app is already built than if you are making the design choice before coding commences

    it's as much based on how disruptive the change will be as it is how much you will gain by redesigning

    ordinarily this type of situation is usually done with a single table, and suitable indexes

    however, the retrofit introduces its own problems (time, effort, cost, re-testing, etc.)

    is this applicaton in production yet?

    also, does your database support views?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    San Francisco
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    leaving it broken out or combining them into one is not the same decision when the app is already built than if you are making the design choice before coding commences

    it's as much based on how disruptive the change will be as it is how much you will gain by redesigning

    ordinarily this type of situation is usually done with a single table, and suitable indexes

    however, the retrofit introduces its own problems (time, effort, cost, re-testing, etc.)

    is this applicaton in production yet?

    also, does your database support views?
    True and true. Thank you for your reply! Out of curiousity, why would you include them all in a single table? Couldn't you argue that it would be faster on the lookups for data with less records (like EMTs or coroners) if they were in a separate table? (I ask to learn, not to contradict you out of hand).

    The app is "built" in the sense that it exists. But it is not in production; it's currently going through a QA process.

    Unfortunately, it's badly designed on the back-end (eg: no code reuse whatsoever; 800 line scripts with no functions, methods, subroutines -- just one large if/then) and the database is a mess. In the future, this behemouth is going to be installed in multiple locations so ideally, I'd like to resolve these issues before that happens.

    I'm embarassed to say that no, my database (mySQL) does not support views. It makes me want to cry.

    Am I insane for attempting a retrofit? I'm hoping that more work now will translate into less work (production, QA) late. Or is this a fool's errand?

    Thank you again!

    Postscript: Whatever decision I make, I must live with -- I'm the poor slob who is going to be supporting this application in the future.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    lookups should be indexed, and will be just as fast if there are millions of rows as if there are thousands

    it's the same as looking for a book at amazon.com, it doesn't matter that there are cds and dvds in the database, it's still a fast search

    how many columns exist which are unique to a specific type, versus how many columns are in common?

    would the admin scripts be a lot simpler if they could all share a large portion of common code, with exceptions for the unique stuff?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    San Francisco
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    lookups should be indexed, and will be just as fast if there are millions of rows as if there are thousands

    it's the same as looking for a book at amazon.com, it doesn't matter that there are cds and dvds in the database, it's still a fast search

    how many columns exist which are unique to a specific type, versus how many columns are in common?

    would the admin scripts be a lot simpler if they could all share a large portion of common code, with exceptions for the unique stuff?
    There are 4 columns unique to a specific record type out of a total of 40. Some of the records also track home information (address, city, phone), while others do not (although they may in the future). If I include those as semi-unique, this brings the former total up to 14 out of 40.

    The admin scripts would be simpler if the code were reused. For example, for some unknown reason the previous developer had all the data being capitalized before it was inserted into the database. This was not in the specs and the client did not want the data munged this way. To remove this, I had to edit out 80 semi-unique instances of a built-in PHP function across 20 or more scripts, which was somewhat maddening. That's a simple example -- but multiple its complexity across fifty or so scripts and things become more difficult.

    That you again, Mr r937! You've been more helpful than you know, and your posts have done a great deal to set my mind at ease.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    good luck and don't forget to post again if you have further questions

    the question about the views was that if you consolidate your tables into one, you can declare views using the former table names, and the code will still work while you rework/rewrite it in stages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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