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

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

Healdem, I am assuming that by this you are referring to the normalization of data.

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

denormalize 90 tables into a couple??

i would like to see you try that

Don't think it can't be done. Ever worked a gov't salvage project?

It's called a straight VSAM Conversion to a table format

I bet Mike could work up an EAV layout that would do this.

EAV.. Oh God.....

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

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!

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

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:

How does one go about troubleshooting something like that? I was always curious about that.

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.

This is actualy good advice Mike, thanks. I think I will ask Rudy or Blindman for their advice.

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.

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.

Stop. Mike, you had me at

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.

pootle flump ur codings are working excelent.