Looking for database design patterns

Hot Topic

Message Routing doesn't work
Windows Problem With Notepad
Windows Problem With Notepad
Forcing Documents to be Read-Only?
SharePoint 2007 ECM for IIS sites (non-SharePoint integrated IIS sites)
Migrate 2.0 to 3.0 on another server
Welcome!
I don't know what can I extract from my load project
Parsing;
Can't find Link Data Sources Wizard in SharePoint Designer

SharePoint Products

When should I create another instance?
Reorting Services and MOSS 2007
Custom action for a Task
SharePoint 2007 ECM for IIS sites (non-SharePoint integrated IIS sites)
Publishing Info Path form
OHSBE Message formats
Publish restores sites...
Application definition file for BDC
where to store a unique ID?
%TEMP% Env Variable

Looking for database design patterns

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



Answer this question

Looking for database design patterns

  • VishalR

    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

  • ArnieSacnewsome

    This is some Good Stuff.

  • RafaBotero

    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

  • allenwfc

    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



  • viv3

    PhilH,

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



  • Looking for database design patterns
Answer this question