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 "http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html"
>A Sane Approach to Primary Keys.
Expanded Table Types
The "http://database-programmer.blogspot.com/2008/01/table-design-pattern-limited.html"
>Limited Transaction Pattern occurs when restrictions
on allowed transactions require one or more additional
unique constraints on a transaction table.
The "http://database-programmer.blogspot.com/2008/02/primary-key-that-wasnt.html"
>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.
"http://database-programmer.blogspot.com/2008/01/table-design-patterns-cross-reference.html"
>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.
- "http://database-programmer.blogspot.com/2008/05/introducing-database-security.html#readonlylookup"
>Read-only Lookup Table.
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 "http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html"
>Denormalization Patterns, we see three distinct patterns:
- "http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html#fetch"
>The FETCH pattern
- "http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html#aggregations"
>The Aggregration Pattern
- "http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html#extend"
>The EXTEND Pattern
Other Patterns
The "http://database-programmer.blogspot.com/2008/04/advanced-table-design-resolutions.html"
>Resolution Pattern occurs when a value may come from more than one place and you must resolve the possibilities into a final choice.
"http://database-programmer.blogspot.com/2008/07/history-tables.html"
>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 "http://database-programmer.blogspot.com/2008/08/advanced-algorithm-sequencing.html">Sequence Dependencies it can be done with
a combination of tables and server-side code.
You can implement "http://database-programmer.blogspot.com/2008/09/advanced-table-design-secure-password.html"
>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 "http://database-programmer.blogspot.com/2008/02/false-patterns-such-as-reverse-foreign.html"
>A Primary Key in Disguise.
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
