locked
Looking for database design patterns RRS feed

  • Question

  • 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://en.wikipedia.org/wiki/Graph_theory
    • 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

    Friday, September 1, 2006 11:15 AM

Answers

  • 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

     

     

     

    Tuesday, September 5, 2006 6:45 PM

All replies

  • PhilH,

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

    Saturday, September 2, 2006 6:15 AM
  • 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
    Sunday, September 3, 2006 4:59 AM
  • 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

     

     

     

    Tuesday, September 5, 2006 6:45 PM
  • Thanks for all that Arnon. Some very good info and I will spend some time going through all your references.

    Temporal Databases

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

    Heres some good references for those interested:

     

    Phil

    Thursday, September 7, 2006 12:18 PM
  • This is some Good Stuff.
    Tuesday, September 19, 2006 2:01 PM