Sure, if you look at the very low level, you do end up with pure
relationships.
But when you look a bit higher I notice two types of patterns:
1. The relationship between tables
2. The similarity between tables
The following example falls within both of the above bullets. I needed
to create a number of different types of users. These users needed to
be distinct much like employees and clients. In my case it was staff,
members and advertisers. I needed to store a number of bits of
information which was similar to all three. Those bits of information
was stored in four tables that are contact_information (email, phone,
etc), identification (first name, last name, etc), address and login
(username , password).
The reason I created three user tables was between each had added
specifications which required 1:1 or 1:m or m:n relationships which were
not required by the other three.
Rich R wrote:[color=blue]
> "Don Vaillancourt" <donv@webimpact.com> wrote in message
> news:E3hCd.87498$vO1.514048@nnrp1.uunet.ca...
>[color=green]
>>Hello all,
>>
>>Over the years as I design more database schemas the more I come up with
>>patterns in database design. The more patterns I recognize the more I
>>want to try to design some kind of generic design patterns that can be
>>used and shared amongst many sub-schemas.
>>
>>For example, the grouping of entities. I may have the following tables:
>>employee, product and client. These tables have no direct relationship
>>with each other. But each can be grouped: employee_department,
>>product_category, client_region.
>>
>>The idea of grouping for those three tables is exactly the same and thus
>>I would decide to perhaps create one table named grouping with the
>>following fields: parent_table_name, parent_table_pk, child_table_name,
>>child_table_id.
>>
>>This would make programing and maintaining of the database and sourcode
>>(Java, ASP, ColdFusion, etc) a lot simpler. Of course there is a limits
>>and drawbacks to such a solution.
>>
>>My question is: Is there any literature on such kind of database design.
>>
>>Thank You
>>
>>
>>
>>--
>>* Don Vaillancourt
>>Director of Software Development[/color]
>
>
> My question is: Is there any literature on such kind of database design.
>
> Thank You
>
> When you think about, there would not be many patterns to talk about.
> Relational database design is constrained by the fact that at the end of the
> day you have only binary relationships to work with. So you have:
>
> 1:0-1
> 1:0-m
> m:m (which resolves to binaries, anyways)
>
> The great thing about relational databases when done right is the semantics
> you can captured in the design. Once you begin to generalize tables,
> columns, and relationships, you lose the semantics.
>
> I've have seen attempts to accomplish what you are talking about, but in the
> long run the resulting design and implementation actually harder to maintain
> and understand then have a normalized database with good naming conventions
> and foreign key constraints. You lose all semantics when the column names
> and values refer to other database objects. Much like the same problems with
> intelligent keys.
>
> If you are thinking along the lines of 'Design Patterns', I just don't think
> there are that many that would make life any easier. A good design makes
> everything easier!
>
> You might be interested in looking at Fowler's 'Analysis Patterns', Inmon,
> et all 'The Data Model Resource Book.' and David Hay's 'Data Model Patterns:
> Conventions of Thought'.
> I haven't read Hay's book. But from Book News, Inc. 'Analyzes data
> structures common to many types of businesses in areas such as accounting,
> material requirements planning, process manufacturing, contracts,
> laboratories, and documents, for analysts who have learned the basics of
> data modeling (or entity/relationship modeling) but who need more insight to
> prepare a model of a real business. Includes b&w diagrams, and a set of
> ready-to-use models for typical applications in many industries.'
>
> Good luck. I'm sure you've started a lively discussion.
>
> Regards,
>
> Rich
>
>[color=green]
>>/ This email message is intended only for the addressee(s)
>>and contains information that may be confidential and/or
>>copyright. If you are not the intended recipient please
>>notify the sender by reply email and immediately delete
>>this email. Use, disclosure or reproduction of this email
>>by anyone other than the intended recipient(s) is strictly
>>prohibited. No representation is made that this email or
>>any attachments are free of viruses. Virus scanning is
>>recommended and is the responsibility of the recipient.
>>/[/color]
>
>
>[/color]
--
* Don Vaillancourt
Director of Software Development
*
*WEB IMPACT INC.*
phone: 416-815-2000 ext. 245
fax: 416-815-2001
email:
donv@web-impact.com <mailto:donv@webimpact.com>
web:
http://www.web-impact.com
/ This email message is intended only for the addressee(s)
and contains information that may be confidential and/or
copyright. If you are not the intended recipient please
notify the sender by reply email and immediately delete
this email. Use, disclosure or reproduction of this email
by anyone other than the intended recipient(s) is strictly
prohibited. No representation is made that this email or
any attachments are free of viruses. Virus scanning is
recommended and is the responsibility of the recipient.
/
