Dear Tom,

Thanks for clearing my doubts. I am now confident ...

Hi tom,

I have a small doubt regarding a design issue.

I have a parent table say P1 that has two child tables say c1 & c2. The both refer the parent table on the basis of same foreign key column(ref_id). But they dont have any records in common, that is to say if record is in C1 that particular record wont exist in C2.

What this kind of data model is called ? or at first instance is it a wise idea to have such structure ? Also, how would we write the query for the same as if i join the child tables, it won't return any rows !?


P1
ref_id
--------------------
1
2
3
4

C1
ref_id
------------------
1
3

C2
ref_id
-------------------
2
4

Thanks in advance

I tried to model a set of tables like these:

CREATE TABLE T1
COLUMN1 PK
...

CREATE TABLE T11
COLUMN11 PK
COLUMN1 FK TO T1
...

CREATE TABLE T111
COLUMN11 FK TO T11
...

CREATE TABLE T112
COLUMN11 FK TO T11
...

All foreign keys are indexed.
I've been asked why I'm not propagating the T1 primary key to all the n level children primary keys to look like this:

CREATE TABLE T11
COLUMN11 PK
COLUMN1 PK & FK TO T1
...

CREATE TABLE T111
COLUMN11 FK TO T11
COLUMN1 FK TO T11
...

CREATE TABLE T112
COLUMN11 FK TO T11
COLUMN1 FK TO T11
...

T1 does not have a parent table role in this model. It's just a table to group the values in T11 and store summarized information. The key records are in T11 and they are the key information in our business. The COLUMN1 from T1 does not have any values to all the other tables which will be referencing T11.
Then I got this reply back from one of my colleagues (our senior DBA)

<<I understand your point, however what is being built is a hierarchal model, not a relational model – as part of any relational model, keys are “automatically” propagated down to the child tables, and they become part of the primary key of the child table, it should not matter you want to query or not, as you cannot always predict the future. These primary keys should not be just foreign keys in the child table, but actually the leading edge of the primary key. It serves two purposes:
• turns it into a relational model rather than a hierarchical model
• ensures you do not have deadlocks on child tables on deletions (not an issue in this case) –ie. all foreign keys should have an index created on it>>

Here are my questions:
1 - Is it true that in any relational model, keys are “automatically” propagated down to the child tables and they become part of the primary key of the child table? What are the pros and cons of this approach?
2 - Are T111 and T112 week entities? Do you recommend creating surrogate primary keys on them?
3 - What really needs to be where in this model?
Please shed some lights

Thanks a lot,

Hi Tom,

I was reading an article on database design Terminology.

</code> http://www.sum-it.nl/cursus/dbdesign/english/intro030.php3#column <code>

1) I could not get the meaning of a text in this site on Many-to -many relationship.
Th text is pasted below
"Most of the time the relation can be made a lot clearer, by adding a link table, at the 'top' or the 'bottom'."

a) when should one put the link table at the 'top' and when on the 'bottom'. what are the deciding factors ?

2) Many to One Relationship
the text is below
"A Many to One relation is the same as one-to-many,but from a different Viewpoint."

a) can you please eloborate this #2 for better clarity.


Thanks in adv
Thirumaran

About 99% of the time when someone draws an M..M relationship and I ask a few questions we find out that it has attributes... so you need to break it out and make it an entity.

For example, in the classic Emp - Dept case if you ask for history you turn the relationship to M..M but then when you realize that you need dates for each assignment to a department you need to add a new intersection entity.

Hi Tom,

I came across some terms which were new to me
"semantic modeling concepts". I had been searching for this information but could not get any clear examples with diagrams (ERD).

1)Specialisation
2)Generalisation
3)Categorisation
4)Aggregation
5)Superclass & Subclass

what are these above modeling techniques used for and where it can be used . Examples will be a greater help
with diagrams.

Thanks in adv
Thirumaran

Hi Tom,

I am confused with respect to IDENTIFYING RELATIONSHIP between two tables. (Parent -> child)

Identifying Relationship will have 1-1 or 1-m relationship and what will be the cardinality on both sides.

Examples will be much helpfull.

Thanks In adv
Thirumaran

Dear Tom,

I have two table
a) INVOICE
b) INVOICE ITEM
The INVOICE ITEM has an optional relationship to the INVOICE.

we create INVOICE ITEMs but may not relate them to an INVOICE.The INVOICE foreign key in the INVOICE ITEM table thus needs to be logically null.
Physically, however, a zero or dummy INVOICE value would heavily skew the foreign key index.

#1) Can i have null foreign key's , will there be any damage as mentioned above.
#2) Is there any other Best alternate design solution for this.

Thanks in adv
Thirumaran

Hi Tom,

My collegue is debating this is wrong.

she is suggesting to use a value equal to the row's own primary key multiplied by negative one, so that the foreign key index values are evenly distributed across all rows, whether or not the foreign key relationship exists.

i.e

INVOICE ITEMs without a related INVOICE receive a foreign key value equal to their primary key multiplied by -1 so that the index over the INVOICE foreign key is evenly distributed.

Thanks in adv
Thirumaran

By adding a primary key to a weak entity set , we can make it a strong entity set. Then Why do we require weak entity set.

Explanation of Index skewing.
The case of having -1 * PK to evenly distribute index is a typical example of avoiding re-index when a link between child and parent is established.

I found a closest example here.
</code> http://www.dbpd.com/vault/9805xtra.htm <code>

Tom, I like your site very much, hope you can help me for the following question.
I am drawing an ERD for an existing database to help the team's application development. To simplify my scenario here-- there are three tables as follows T1 (c1(pk), ...), T2(c2(pk), c1, c3, ...), T3(c4(pk), c3,...), I drew a relationship from T1 to T2 on column c1, so T2 now is like(c2(pk), c1(fk to T1), c3, ...). now should I relate T2 and T3 on column c3? the data in T2 and T3 are related based on column c3, what should I do for the ERD -- since both T2.c3 and T3.c3 are not the primary key in either tables, normally I am not supposed to draw the relationship on it, but in T2 and T3, the current primary key T2.c2 and T3.c4 are meaningless machine generated columns.