Page 1 of 5 123 ... LastLast
Results 1 to 15 of 61
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Now I understand *why*

    I am pretty much a novice when it comes to database modeling and have had an opportunity to model a rather large database with about 90 tables. Well.. I say large because I couldn't begin to fathom anybody creating something larger without going absolutley bonkers.

    Anyway, I figured I would share some of my experiences and things that were not clear to me before about why software developers and data modelers really could not see eye to eye.

    The datamodeler wants to normalize the data, and rightfully so. The developer wants to develop the program with the least amount of work, stress and headache. Since I am doing both, I am really beginning to understand why throwing all of the data into a couple of tables appears very tempting. Although I would never do that, it really is tempting at times.

    I am seeing that when data is normalized, the sql to bring it all back together again becomes quite a task and gets really involved. I would venture to say that had I not normalized my data I could have probably finished my design by now. Its an inner struggle really.

    Frank

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    yes its quite possible that you could have knocked up a physical design which would meet the current requirements....

    ..providing of course
    ....you had a comprehensive full discovery of facts
    ....you were confident that your users /customers were not going to change their minds, processes or business model
    ....you could walk away from the project after completion and 'know' that any problems would never ever come your way.

    the whole point of designing the schema is to support the way the business works right now AND in the future.. its pointless deploying a design which works right now, but fails in the near future as the business changes. Its dumb to design something which works now, but falls apart over the life of the application
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by healdem
    the whole point of designing the schema is to support the way the business works right now AND in the future.. its pointless deploying a design which works right now, but fails in the near future as the business changes. Its dumb to design something which works now, but falls apart over the life of the application
    Healdem, I am assuming that by this you are referring to the normalization of data.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    By all means, do not normalize your data...I mean, there is only decades of study based on the relational model.

    Make sure your developers have it as easy as possible....

    So when the f up the data, you have the luxury of correcting all of the data anomalies

    Good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    ...I am really beginning to understand why throwing all of the data into a couple of tables appears very tempting.
    denormalize 90 tables into a couple??

    i would like to see you try that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Don't think it can't be done. Ever worked a gov't salvage project?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's called a straight VSAM Conversion to a table format
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    denormalize 90 tables into a couple??

    i would like to see you try that
    I bet Mike could work up an EAV layout that would do this.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    EAV.. Oh God.....

    I figured this would have been a good post for Mike_Bike_Kite to chew on.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Franky baby (somebody better get that reference, or I just look... odd!), good for you for not cutting corners and doing the things the way they really should be

    SQL is no easy thing to learn, but by god, when you're working with normalized data it is far less of a headache!
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    No .... just ..... can't ........ stop ............. myself ........

    EAV was a discussion that's now over - I do see that it has a valid place but only for scenarios where there is a tonne of continual change, performance isn't your main criteria and you don't mind a few compromises. None of that applies here so even I wouldn't even consider it here.

    90 tables suggests a fair size system which, if it's important to your company, may be worth hiring a database expert to have a quick look over your design to highlight any possible issues. I'm sure you've done a fine job but you describe yourself as a novice and most novices start with small systems and work their way up rather than the other way round

    3NF is always the first place to aim for in your database design but I think that good database designers should also try to think about how the developers will eventually access the data - if it's difficult to understand then it's likely to be used wrong, and even a perfect relational design that's difficult to use will earn itself a bad name.

    We've had some rather trivial problems to solve recently so why not describe the situation (in the db concepts forum) and see what's suggested - at best you might get a few good ideas - at worst we all have a laugh

    Mike

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    Franky baby (somebody better get that reference, or I just look... odd!), good for you for not cutting corners and doing the things the way they really should be

    SQL is no easy thing to learn, but by god, when you're working with normalized data it is far less of a headache!
    Hey Georgie... Your right. I am seeing how easy it is to change minor things as I need to in the data model. It sure doesn't make my life easy while making the front end though. *There* is where the developer has it rough. The more joins, the more complex it becomes for the developer. But its ok, I like the experience.

    I didn't want t cut any corners on the data design. I can't imagine what a terrifying thing it would be or even the level of complexity that is involved in finding a data anomaly.

    A while back I purchased a php classifieds website that used mysql. The db had about 70 tables but was not at all normalized. After about 1000 ads were placed on the board, one of the sellers called me and wanted to know what happened to a couple of his ads. They were gone.. I have no idea where they went and they was nothing in the transaction log either.

    Look at the DDL form just one of the tables:

    CREATE TABLE `classifieds` (
    `id` int(14) NOT NULL auto_increment,
    `seller` int(13) default NULL,
    `stockno` varchar(10) default NULL,
    `live` tinyint(4) NOT NULL default '0',
    `title` tinytext,
    `date` int(14) default NULL,
    `description` text,
    `precurrency` varchar(25) NOT NULL default '',
    `price` varchar(50) NOT NULL,
    `postcurrency` varchar(25) NOT NULL default '',
    `image` tinyint(4) default '0',
    `category` int(11) default NULL,
    `duration` int(11) default NULL,
    `location_state` tinytext,
    `location_country` tinytext NOT NULL,
    `location_zip` varchar(10) default NULL,
    `ends` int(14) default NULL,
    `search_text` mediumtext NOT NULL,
    `viewed` int(11) NOT NULL default '0',
    `transaction_type` tinyint(4) NOT NULL default '0',
    `cc_transaction_type` tinyint(4) NOT NULL default '0',
    `bolding` tinyint(4) NOT NULL default '0',
    `better_placement` tinyint(4) NOT NULL default '0',
    `featured_ad` tinyint(4) NOT NULL default '0',
    `subtotal` double(5,2) NOT NULL default '0.00',
    `tax` double(5,2) NOT NULL default '0.00',
    `total` double(5,2) NOT NULL default '0.00',
    `customer_approved` tinyint(4) NOT NULL default '0',
    `expiration_notice` int(11) NOT NULL default '0',
    `email` tinytext NOT NULL,
    `pedigree_s` tinytext NOT NULL,
    `pedigree_ss` tinytext NOT NULL,
    `pedigree_sd` tinytext NOT NULL,
    `pedigree_sss` tinytext NOT NULL,
    `pedigree_ssd` tinytext NOT NULL,
    `pedigree_sds` tinytext NOT NULL,
    `pedigree_sdd` tinytext NOT NULL,
    `pedigree_d` tinytext NOT NULL,
    `pedigree_ds` tinytext NOT NULL,
    `pedigree_dd` tinytext NOT NULL,
    `pedigree_dss` tinytext NOT NULL,
    `pedigree_dsd` tinytext NOT NULL,
    `pedigree_dds` tinytext NOT NULL,
    `pedigree_ddd` tinytext NOT NULL,
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1868 DEFAULT CHARSET=latin1;
    How does one go about troubleshooting something like that? I was always curious about that.

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by mike_bike_kite
    No .... just ..... can't ........ stop ............. myself ........

    EAV was a discussion that's now over - I do see that it has a valid place but only for scenarios where there is a tonne of continual change, performance isn't your main criteria and you don't mind a few compromises. None of that applies here so even I wouldn't even consider it here.
    Hi Mike. I'm glad to see that you have come a long way from your EAV post. I just wrote you off from that point but it looks like you have made progress in your understanding of how important normalization is.

    I personally would trade performance any day of the week for a database that *I know* beyond a shadow of a doubt is going to give me correct information when I need it. I just couldn't see doing it any other way, really. You have slow performance, go gat a faster server.

    90 tables suggests a fair size system which, if it's important to your company, may be worth hiring a database expert to have a quick look over your design to highlight any possible issues. I'm sure you've done a fine job but you describe yourself as a novice and most novices start with small systems and work their way up rather than the other way round
    This is actualy good advice Mike, thanks. I think I will ask Rudy or Blindman for their advice.

    3NF is always the first place to aim for in your database design but I think that good database designers should also try to think about how the developers will eventually access the data - if it's difficult to understand then it's likely to be used wrong, and even a perfect relational design that's difficult to use will earn itself a bad name.
    That seems fair.. In this case, it seem to me that the database designer and the developer need to work hand in hand to iron out any questions or concerns so they can stay on the same page.

    We've had some rather trivial problems to solve recently so why not describe the situation (in the db concepts forum) and see what's suggested - at best you might get a few good ideas - at worst we all have a laugh
    I have posted numerous times in that forum before and received some really sound advice that I have used in this model. I'm sure that my model is not perfect but I do believe that my data will not suffer from any serious data anomalies. Especially with the front end.
    Last edited by Frunkie; 08-15-07 at 17:24.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Stop. Mike, you had me at
    Quote Originally Posted by mike_bike_kite
    3NF is always the first place to aim for in your database design but I think that good database...
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you\ there is another decent SQL developer get the FE developers requirements and produce views to flatten out the structure for them. Don't get tempted into producing a few uber-views - a fair few tight tight, specific ones should help them out. Better still implement a database API via stored procedures. Get the business requirements from the developers and produce sprocs to meet their requirements. That way you concentrate on efficient SQL code and they concentrate on the other two tiers.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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