Sunday, January 20, 2008

Table Design Patterns

This entry lists all of the Table Design Patterns that I have described in the blog entries. I will update it whenever a new pattern is described.

Basic Table Types

These patterns describe the kinds of things that you store in tables. Each pattern is characterized by the relative number of columns and rows, and whether it stores either information about permanent things or interactions between permanent things.

These patterns were described in the entry on A Sane Approach to Primary Keys.

Pattern Name Relative Column Count Relative Row Count Type Notes
Reference Small Small Permanent Use single-column character primary key.
Small Master Small Small Permanent Use single-column character primary key.
Large Master Large Large Permanent Use integer auto-assigned primary key
Transactions n/a n/a Transient Describes interactions between things, like a customer purchase of an item or a student's enrollment in a class. Use integer auto-assigned primary key
Cross Reference n/a n/a Permanent Describes relationships between master entries, such as an item's price group or a teacher's department. Use multi-column primary keys.


Expanded Table Types

The Limited Transaction Pattern occurs when restrictions on allowed transactions require one or more additional unique constraints on a transaction table.

The Impermanent Primary Key pattern occurs when a value that is a good choice for a natural key will change from time to time. For this pattern we use a pair of tables to track the entity.

Foreign Key Patterns

There are two fundamental kinds of foreign key, which correspond to the "master table" and "transaction tables" types.

The cross-reference validation pattern occurs when an entry must be validated against some previously defined relationship between master items.

Secure Patterns

Some table patterns depend upon security as a basic part of their definition. Different combinations of SELECT, INSERT, UPDATE, and DELETE permissions can replace complex application logic with zero-code server-implemented solutions.

Denormalization Patterns

Many seasoned database programmers denormalize their databases for a variety of reasons. Like all database activities, these also follow patterns. In the post Denormalization Patterns, we see three distinct patterns:

Other Patterns

The Resolution Pattern occurs when a value may come from more than one place and you must resolve the possibilities into a final choice.

History Tables provide three major benefits. They provide an audit trail of user actions, they give you the ability to reproduce the state of a table at some prior time, and if they are cleverly designed they can produce very useful aggregate numbers such as a company's total open orders for any given day in the past or the total change in open balances in any arbitrary period of time.

If you need to Sequence Dependencies it can be done with a combination of tables and server-side code.

You can implement Secure Password Resets entirely in the database server.

Anti-patterns

Sometimes user requirements appear to call for things that are impossible to do. When the analysis leads to one of these patterns it may seem like a dead-end, but there are usually valid patterns hiding beneath these.

  • When user requirements say "If X happens then Y may not happen" some analysts will see this as saying an entry in table A prohibits an entry in table B. This is a Reverse Foreign Key, which does not exist and cannot be implemented, it is an anti-pattern. These are often A Primary Key in Disguise.

33 comments:

Armando Iswahyudi said...

the kartolewo
mebel jepara
mebel jati jepara
1936memoriahistorica.blogspot.com/

Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.

Vanesha Prescilla said...

Ayam Sabung Bangkok

sharon said...


Thanks for sharing such a great information with us.Keep on updating us with these types of blogs.
professional web design company in chennai
CRM software development company in chennai
ERP Software Development Company in chennai
SEO Company in chennai
SMO company in chennai
SEM company in chennai
SEM Services in chennai
web portal development company in chennai
web portal development services in chennai
twitter marketing company in chennai

Destiny Solutions LLP said...

paypal quickbooks integration

straightener said...

شركة تنظيف الفلل في ابوظبي
افضل شركة تنظيف سجاد بالبخار بدبي
شركة تنظيف خزانات في دبي رخيصة
شركة تنظيف في الشارقة
شركة تنظيف بداخل أحياء ومدن دبي
شركة تنظيف سجاد وموكيت بالشارقة
شركة تنظيف مجالس الشارقة
شركة تنظيف بدبي
افضل شركة تلميع وجلي الرخام في دبي
أفضل شركة تنظيف الفلل والسجاد والكنب والخزانات المياة في العين
افضل شركة تلميع وجلي الرخام في دبي

Yourdoorstep said...

Thanks for Fantasctic blog and its to much informatic which i never think ..Keep writing and grwoing your self

Birth certificate in delhi
Birth certificate in ghaziabad
Birth certificate in gurgaon
Birth certificate in noida
How to get birth certificate in ghaziabad
how to get birth certificate in delhi
birth certificate agent in delhi
how to download birth certificate
birth certificate in greater noida
birth certificate agent in delhi
Birth certificate in delhi

softwareservicescompany said...

I have scrutinized your blog its engaging and imperative. I like your blog.
custom application development services
Software development company
software application development company
offshore software development company
custom software development company

Java application development said...

I have perused your blog its appealing and worthy. I like it your blog.
java software development company
Java web development company
Java development companies
java web development services
Java development company

mobile application development said...

Amazing Post. Your writing is very inspiring. Thanks for Posting.
Mobile App Development Company in chennai
mobile app developers in chennai
best mobile application development companies
Mobile apps development companies in chennai
mobile app development chennai

best ppc company in india said...

I have inspected your blog its associating with and essential. I like it your blog.
ppc services india
ppc management services
ppc services in india
ppc advertising services
ppc marketing services
pay per click advertising services

Naatu Maatu Paal Chennai said...

Aran’s traditional milk is pure A2 milk, Nattu Kozhi Muttai Chennai, Organic Milk Chennai, A2 Milk Chennai, Cow Milk Chennai, Naatu Maatu Paal Chennai Chennai hand-milked in a traditional way from healthy native Indian breeds and reaches your doorstep.

Milking Process
The milking is done from indigenous cows by using hands. No machines are used in order to ensure no harm is done to the cows

Packing Methods
As soon as milking is done, the milk is filtered and packed in the FSSAI certified place with hairnets and gloves on this packing is done into the 50 microns wrappers which are not reactive to the food items. Again, no machines are used for packing to contribute to the environment, as they consume more water and power.

Milk Delivery
As soon as packing and quality check are done, the milk packets are collected and brought for delivery.

php development company said...

I have perused your blog its appealing, I like it your blog and keep update.
php development company,
php development india,
php application development,
php website development,
php web development,
php framework,
Thanks for sharing useful information article to us keep sharing this info

Unknown said...

Given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped:
Erp In Chennai
IT Infrastructure Services
ERP software company in India
Mobile Application Development Company in India
ERP in India
Web development company in chennai

Vinita Hotwani said...

roku.com/link this is simplest way to stream entertainment to your TV. On your terms. With thousands of available channels to choose from for more information. roku.com/link

Hardeep said...

Shield your gadget from malware and infections by downloading, introducing mcafee activation code and after that actuate McAfee item. It is a mainstream and surely understood antivirus programming present in the market. McAfee offers total answers for shielding gadgets from malware, infections, and different dangers.

Hardeep said...

Office Remote changes your phone into a splendid remote that collaborates with Office office/setup on your PC. The application allows you to control Word, Excel, and PowerPoint from over the room, so you can walk around wholeheartedly in the midst of introductions.

Hardeep said...

Activation is the most significant technique as the clients are not ready to use the administrations and utilizations of the introduced office install programming except if it has been initiated.

Hardeep said...

Get a victor among the best Office course of action support! We offer MS Office 365 course install office of action and MS Office 2016 game plan associations. In addition, our point is to clear the majority of the issues of your pc , PC, and so forth.

Hardeep said...

Here you can get thing keys for Office 365 activate office full structure and individual Office apps.Here we are giving full help to present pack.

Hardeep said...

McAfee is a pioneer in online helplessness security. McAfee, the gadget to-cloud cybersecurity organization, gives security arrangements that shield information and prevent dangers from gadget to cloud utilizing mcafee product key an open proactive, and knowledge driven methodology.

Hardeep said...

McAfee has complete digital security arrangements. Get the McAfee item and download, introduce, and initiate enter mcafee key code McAfee effortlessly. Access your McAfee account through ensure boundless gadgets with McAfee's finished infection assurance and Web Security.

Hardeep said...

Outstanding specialized help for setting up office activation in your PC or laptop.just call us on our number and we are prepared to give you the best help.

Hardeep said...

Norton Security covers PCs, Macintoshes, Androids, iPads and iPhones. Not all highlights are accessible on all stages. Sign in to My Norton norton login to deal with your membership, update your profile, change your charging inclinations, or reestablish your Norton security.

Hardeep said...

Ensure every one of your gadgets with your Norton membership norton security My Norton makes it simple for you to set up your Norton insurance check your security status, and stretch out your assurance to different PCs, Macintoshes, cell phones, and tablets.

Hardeep said...

Exceptionally prominent among the PC clients, the Norton antivirus norton antivirus programming has been disposing of malware, infections and different sorts of on the web and disconnected dangers from influencing the presentation of a PC for a considerable length of time.

Hardeep said...

My Norton makes it straightforward for you to set up your Norton affirmation, check norton com setup your security status, and loosen up your protection to various PCs, Macintoshes, mobile phones, and tablets.

Hardeep said...

Especially composed guide how to download,install and start download office your MS Office 365, Office 2016 etc. on any contraption. If you have find any issue in making your Office account.

Hardeep said...

Office is straightforwardly open as a one-time buy for business and purchaser clients. Office 2019 is accessible for activate office 2016 the two Windows and macOS, and unites model varieties of Word, Exceed expectations, PowerPoint, and Viewpoint.

Hardeep said...

McAfee Virtual Expert distinguishes and settles numerous item issues on your PC. In the event that MVT can't resolve the issue, the information gathered can be utilized by download mcafee your help specialist to settle the issue.

Hardeep said...

Highly popular among the PC users, the Norton antivirus norton login software has been eliminating malware, viruses and other kinds of online and offline threats from affecting the performance of a computer for years.

Hardeep said...

Roku is a streaming device, which is a reasonable roku setup roku code link and other Set-up Box. Roku is a bundle of amusement, where client can stream for boundless motion appears, web arrangement, news, animation and a lot more projects.

officecomoffiice said...

General process of office.com/setup is already mentioned above, however from the starting till the last process of activating you may be face some technical issues.

w-nortoncomnorton said...

norton setup downloaded from the norton.com/setup page helps to work and explore the ever-changing digital landscape. Norton makes it as easy to protect and manage your devices while installing an application on your smartphone.