The Data Model Resource Book

Len Silverston

Mentioned 6

A quick and reliable way to build proven databases for core business functions Industry experts raved about The Data Model Resource Book when it was first published in March 1997 because it provided a simple, cost-effective way to design databases for core business functions. Len Silverston has now revised and updated the hugely successful First Edition, while adding a companion volume to take care of more specific requirements of different businesses. Each volume is accompanied by a CD-ROM, which is sold separately. Each CD-ROM provides powerful design templates discussed in the books in a ready-to-use electronic format, allowing companies and individuals to develop the databases they need at a fraction of the cost and a third of the time it would take to build them from scratch. With each business function boasting its own directory, this CD-ROM provides a variety of data models for specific implementations in such areas as financial services, insurance, retail, healthcare, universities, and telecom.

More on Amazon.com

Mentioned in questions and answers.

This is a question not really about "programming" (is not specific to any language or database), but more of design and architecture. It's also a question of the type "What the best way to do X". I hope does no cause to much "religious" controversy.

In the past I have developed systems that in one way or another, keep some form of inventory of items (not relevant what items). Some using languages/DB's that do not support transactions. In those cases I opted not to save item quantity on hand in a field in the item record. Instead the quantity on hand is calculated totaling inventory received - total of inventory sold. This has resulted in almost no discrepancies in inventory because of software. The tables are properly indexed and the performance is good. There is a archiving process in case the amount of record start to affect performance.

Now, few years ago I started working in this company, and I inherited a system that tracks inventory. But the quantity is saved in a field. When an entry is registered, the quantity received is added to the quantity field for the item. When an item is sold, the quantity is subtracted. This has resulted in discrepancies. In my opinion this is not the right approach, but the previous programmers here swear by it.

I would like to know if there is a consensus on what's the right way is to design such system. Also what resources are available, printed or online, to seek guidance on this.

Thanks

As a parallel to my question about Useful stock SQL datasets, I wonder if anyone has come across a library/web page/wiki/etc. that contains stock SQL data models. For example, the problem of maintaining table(s) for login credentials is faced by countless developers. Are any of you aware of a data model that provides a best-practices implementation of this table? What about other use cases? I'm thinking about things like inventory control, sales reporting, application events (login, logout, viewing a given page or item, etc.). I came across a great resource at databaseanswers.org, but wonder if there are any other resources I should take a look at.

As with my question on data sets, I'm looking for things that can readily incorporated into an application, fulfill a generic need that many developers have, and something that is not specific to a single industry or business sector. Address books, login credential tables, messaging, etc. all fall into the criteria I'm thinking about.

Any thoughts?

Have you seen the SO question relational-database-design-patterns?

EDIT: Having got interested in this question I had a look for any books on the subject and found the following volumes:

The Data Model Resource Book: A Library of Universal Data Models for All Enterprises: Vol 1

The Data Model Resource Book: A Library of Universal Data Models by Industry Types: Vol 2

The Data Model Resource Book: Universal Patterns for Data Modeling Vol 3

I've not read the series, so I can't recommend them myself, but they look interesting.

I found http://www.databaseanswers.org/data_models/ very useful. Any other suggestions?

I want to study an ERP system. For that I want to study its processes. So if anyone can give me some useful links then it will be helpful to me, thanks.

Check out The Data Model Resource Book series from Silverstron. Not UML... but close. Volume 1, volume 2.

I'm looking for a resource that shows common design patterns or best practices for data models. For instance, a [very simplified] course model might have:

Student has many Enrollments

Course has many Sections

Sections has many Enrollments

There seem to be many approaches to setting up data models and relationships, and some are more obvious than others. Often, I find myself thinking about it one way and finding out much later that there is a quirk or complication I missed.

For more complex systems, like ERP, Reservations, eCommerce, etc., there must be some standards and best practices but I can't find them anywhere. Any resource suggestions would be welcome. I've googled around quite a bit, but I didn't see any that made sense.

This is a copy of my answer on DBA.

David Hay's

Len Silverston's

Michael Blaha's Patterns of Data Modeling. This one has some interesting temporal, graph, and tree models.

Martin Fowler's Analysis Patterns. This one skims some of the other patterns, but gives accounting a solid treatment.

They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on safaribooksonline.

Also, OASIS's Universal Business Language, schemas

I'm interested in reading a resource (book, or perhaps a web site) that covers normalization of database tables for common scenarios. I'm looking to store contact data for organizations, which includes addresses, phone numbers, as well as location data for places that may be any where in the world, and others.

I've read resources on database normalization theory, and have a decent understanding of 3NF, but I'm sure someone has already solved the problems I'm trying to solve, as they're extremely common. I'd like to review someone else's database design to see how (and maybe why) they chose to do it the way they did.

Any recommendations?

I suggest searching for 'Data Modeling Patterns'. A couple of books I found are Data Model Patterns: Conventions of Thought and The Data Model Resource Book. I have not read either, but the first seems to be the most revered discussing this topic, sort of the 'Martin Fowler of modeling. Another resource to keep in mind is The Vietnam of Computer Science - 'strict' normalization may not always be the best for your particular application.