Re: data modeling

On Wed, 31 Oct 2001 06:32:13 GMT, "WKC" <wkchen_at_bigfoot.com> wrote:

>Under what circumstances that a table should reference/link to itself? I 've
>seen ppl do that. Is it a good practice to have that kinda of design?
>
>And also how much of a performance sacrify when a query involving joining 2
>or more tables, compare to a single table inqury?
>
>any input is appreciated. thanks
>
>-Ken
>

 I do not know what ppl means, but anyway:

1 If you have hierarchical relationships in your design, and the number of levels in your hierarchy is unlimited the best solution is to use a reference to itself.
Consider the emp case. A non-self referential solution is not going to work as any employee can have a unlimited number of people higher in the hierarchy. If any parent will always have only *children* and *never* have *grandchildren*, in that case you could *consider* using a two-table design

2
Your priorities are completely wrong.
The *first* priority in data modeling is having a *normalized* design. A *normalized* design means you won't have update and delete anomalies.
Consider the case where you for the sake of your *myth* of performance sacrifices *merge* the dept and emp table into one table. What is going to happen when you need to have the data of dept 10 alone?
What is going to happen when you need to update the location of department 20.
I strongly *urge* you to study normalization. I say this because you will run from one problem into another if you don't. Performance shouldn't be an issue. In some cases you are going to notice your 3NF data model is resulting in performance problems, in that case you can consider *controlled* redundancy. You seem to be heading in the direction of a fully *un*normalized datamodel. You will regret this soon.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Oct 31 2001 - 02:01:42 CST