SQL Server Schema Design

re: SQL Server Schema Design

test user — Mon, 03 Mar 2008 20:42:09 GMT

Yep - this is a classic example of an EAV design, and "strange" is a kind adjective to use for it.

re: SQL Server Schema Design

braddeem — Mon, 03 Mar 2008 19:40:44 GMT

I understand what the original database architect was trying to accomplish with this schema. I would have never designed anything like that myself however, until I began reading The Data Model Resource Book, Revised Edition, Volume 1, A Library of Universal Data Models For All Enterprises ISBN: 978-0-471-38023-8.

This is similar to a modeling technique show initially in the 2nd chapter of this book in that there is a Value Column with a Type Column. The end goal is to create a very flexible data model.

Consider going to the attribute approach you mentioned. It's easy to decide to make columns for Phone, Cell Phone, Pager, Email, Home Phone, Work Phone... etc. However, what happens when someone has multiple work phones or cell phones? The attribute approach quickly falls apart.

I'm not directly advocating one approach over the other, but I'm very interested in exploring the approach from the original schema as a viable option. Exploring meaning implementing this from the physical data model to the webpage/form UI to reporting to importing data.

Perhaps this is a discussion better suited for the forums of the book above, but regardless the parallels are worth noting.

re: SQL Server Schema Design

Bradley — Mon, 03 Mar 2008 17:58:10 GMT

This is similar to a modeling technique show initially in the 2nd chapter of this book in that there is a Value Column with a Type Column. The end goal is to create a very flexible data model.

Perhaps this is a discussion better suited for the forums of the book above, but regardless the parallels are worth noting.