Looking for database design patterns  
Author Message
PhilH





PostPosted: Architecture General, Looking for database design patterns Top

Hi all,

I am looking for some good references or guidance on database design.

Some of the issues I would like to research include:

  • Storing hierarchies. From what I can gather there are 3 main ways to do this.
  • Storing graphs. By this I mean graphs with vertices and edges, not graphics or images. i.e. see http://www.hide-link.com/
  • Recording and reporting on changes to items over time. Through, for example, History tables or using Insert-only tables.
  • Custom item types. The ability for customers to define their own types of items.
  • Custom metadata. Ability for customers to add fields to items. Some ways I have seen to do this include: extension table per type, store custom data in an XML column, or have a table that stores 1 record per custom property value (ie with columns something like: OwnerItemID, CustomPropID, Value.).
  • Versioning. eg. Item type ‘A’ gets released to customers with fields 1, 2, and 3. Customers have the ability to extend ‘A’. At some time in the future a new version of the software is released where ‘A’ has changed. How can we distribute this to customers without breaking their customizations

Thanks in advance.
Regards,

Phil



Architecture2  
 
 
Diego Dagum





PostPosted: Architecture General, Looking for database design patterns Top

PhilH,

have you taken a look at the Data section at the MSDN Solution Architecture site



 
 
PhilH





PostPosted: Architecture General, Looking for database design patterns Top

Hi Diego

Thanks for responding Diego. I looked through the Data section a while back and it has some good stuff. I need to go back and refamiliarize myself with the info there.

However I suspect it's not quite what I am looking for. I am after high level patterns -- something at the same sort of level as Martin Fowler's PoEAA but applying to database design. BTW I have the Refactoring Databases book (Ambler, Sadalage), but that addresses more low-level, nuts-and-bolts issues rather than high-level design.

Regards,
Phil

 
 
Arnon Rotem Gal Oz





PostPosted: Architecture General, Looking for database design patterns Top

I don't know about any one source for database design (for stuff beyond the normal-form rules)

Maybe I can help with few of your questions:

for hierarchies: take a look at http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html. Note that on newer versions of leading databases you can also store XML and perform xpath queries. Additionally in SQL 2005 you have a neat new way to handle recursive queries with Common Table Expressions (see http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp)

Regarding Graphs: you can take similar approaches as with hierarchies - watch out for circles though ( CTEs can solve that with MAXRECURSION).

Regarding Recording History - One approach that I used is to create a view in front of the real table and set the update trigger to insert into the underlying table. A variation on this is to insert to the an History table. The nice thing about using a view is that the application is unaware of the fact that that the underlying table is a insert-only one

Regarding Custom meta data and types - It depends on what you want to do with them - if it is mainly storage you can just serialize whatever you used to store the type to a BLOB. the options you mentioned are needed if you need more fine grained control

Versioning is a much more complicated issue - you probably want to look at things like ETL (or SSIS if you use SQL 2005) - you will most likely need to create specialized scripts to handle such upgrades

HTH,

Arnon



 
 
PhilH





PostPosted: Architecture General, Looking for database design patterns Top

Thanks for all that Arnon. Some very good info and I will spend some time going through all your references.

Temp****Databases

Since posting my original question I have done some research and found out quite a bit about one of the areas - temp****databases. I referred to these as "insert-only" databases in my first post.

Heres some good references for those interested:

Phil


 
 
GetCode





PostPosted: Architecture General, Looking for database design patterns Top

This is some Good Stuff.