标签云

微信群

扫码加入我们

WeChat QR Code

Design patterns are usually related to object oriented design.Are there design patterns for creating and programming relational databases?Many problems surely must have reusable solutions.Examples would include patterns for table design, stored procedures, triggers, etc...Is there an online repository of such patterns, similar to martinfowler.com?Examples of problems that patterns could solve:Storing hierarchical data (e.g. single table with type vs multiple tables with 1:1 key and differences...)Storing data with variable structure (e.g. generic columns vs xml vs delimited column...)Denormalize data (how to do it with minimal impact, etc...)


I'll lay claim to the best Q&A here for hierarchical data storage: stackoverflow.com/questions/4048151/…

1970年01月01日00分03秒

There are young community for database patterns. dbpatterns.com

2019年06月26日28分06秒

According to our on-topic guidance, "Some questions are still off-topic, even if they fit into one of the categories listed above:...Questions asking us to recommend or find a book, tool, software library, tutorial or other off-site resource are off-topic..."

2019年06月26日28分06秒

RobertColumbia the question was on-topic in 2008, when asked...

2019年06月25日28分06秒

Check out this list of design pattern resources on relational databases and many areas of software engineering github.com/DovAmir/awesome-design-patterns

2019年06月25日28分06秒

The book is titled [Refactoring Databases: Evolutionary Database Design][1] by Scott W. Ambler and Pramod J. Sadalage and is indeed very good.[1]: ambysoft.com/books/refactoringDatabases.html

2019年06月25日28分06秒

Regarding Ambler book: No, you can't list "inserting a column" or "creating FK constraint" as a pattern for the same reason The Gang of 4 book doesn't list the "for" loop being a pattern.

2019年06月25日28分06秒

It's not a pattern it's a refactoring. Like extract method, or rename parameter. Refactoring and patterns go hand in hand.

2019年06月25日28分06秒

One to add: "Analysis Patterns" by Fowler. Similar to Hay's stuff

2019年06月25日28分06秒

Len Silverston's Volume 3 is the only one I would consider as "Design Patterns." The first 2 show sample data models which was common in the time frame the books were written. Volume 3 though actually has multiple design patterns for a given problem scenario. E.g., chapter 4 covers hierarchies/aggregations/peer-to-peer scenarios, and then offers multiple designs that address those WITH pros and cons of each.

2019年06月25日28分06秒

databaseanswers.org/tutorial4_db_schema/index.htm Was looking for very easy to digest information, and this exceeded my expectations. Thanks for the link.

2019年06月25日28分06秒

This is really good info, but I don't see how it qualifies as "design patterns". Any number of those schema may follow various patterns, but doesn't really give knowledge about those patterns.

2019年06月26日28分06秒

This is a great collection of how their firm has satisfied requirements of past customers.Don't necessarily cut and paste them.Make sure you adjust for you own requirements.I saw a couple where I would have implemented them a bit differently in MY situation.Regarding the question of it they are "patterns" - if your idea is the "Gang of Four" style - then no.If you need some good examples of how solutions were implemented previous - then yes.

2019年06月26日28分06秒

Some other patterns I've seen are multi-parent child table (ie, like a global notes with a objecttype and objectid that can link to any other table), or a self-referential FK (ie, employee.manager -> employee.id).Also I've used a singleton config table that has many many columns.

2019年06月25日28分06秒

Why exactly is a mixed-use database an anti-pattern. What am I meant to do if I want to pull reports from a database?

2019年06月25日28分06秒

lhnz: You can't pull a lot of large reports from a transactional database design -- locking for reporting will slow down transactions.Complex joins (performed over and over again) are another knock against transaction performance.You can't do both in one database.To do a lot of large reports, you must move the data into a star schema.The star schema pattern is optimized for reporting.And moving the data removes any lock contention.

2019年06月26日28分06秒

Would normalising the schema reduce row lock contention if you're making the tables hold more "cohesive" data? My thinking is that if a large table was servicing writes to 2 kinds of data sets but both are in the same row, this would result in unnecessary lock contention.

2019年06月25日28分06秒

Seems like Celko's page is gone now. Here's the link to the last snapshot of his page on the Internet Archive.web.archive.org/web/20100103051037/http://www.celko.com/…

2019年06月25日28分06秒

Although your comments are quite instructive and useful, they are not design patterns. They are best practices. Thanks,

2019年06月25日28分06秒

I disagree with the recommendation for unique column names.I'd rather say customer.id to disambiguate than to say customerid even where there is nothing to disambiguate.

2019年06月25日28分06秒

UPSERT is a command and part of the SQL language. It is not a pattern.

2019年06月25日28分06秒

UPSERT is a command in some variants of the SQL language - a number of platforms don't have it, or only got it recently.

2019年06月25日28分06秒

ToddR - I've heard said (slightly cynically) that "patterns" are really nothing more than shortcomings in a language or model, that the user must create work-arounds for. I don't know what UPSERT does, but while it has been added to some SQLs and not others, it is a pattern.

2019年06月25日28分06秒

what about database inheritance like (person, customer, employee) maybe that kind of thing could be considered as design pattern ?

2019年06月26日28分06秒