Database Design Question

Hi Guys

I have one parent entity that is related to two other entity with one to many relatetion ship (i,e main entitys primary is becoming part of primary key of other entitys i,e Identifying relation ship).when these
two entitys are involved in many to many.(one column (primary key of main entity) is common in both the entitys).then what would be primary key of intersetion table or associative table.

ex : A(projId) --> B(ProjId,P1)
-->C(ProjId,P2)

When B and C are involved in manay to many realteionship
ProjId is common in both the entitys.

(... ) is primary key

Can any body explain what would be the scenario.

Thanks
UmashankarI may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott|||Thanks hmscott.

I am trying to understand the existing database datamodel.

In this Project ,Task,Assignment,Resource are four entitiys.

Primary Key Information:

Project :Proj_Id
Task :Proj_id,Task_uid
Resource :Res_uid,Proj_id
Assignment :Assn_uid,Proj_id

In a given model thare is
1.one to many relationship(identifying) between Project and Task.
2.one to many relationship(non Identifying) between Task and Assignments.
3. one to many relationship(non Identifying) between Resource and Assignments.

Surprising to me thare is no relationship shown between project and Assignments in datamodel diagram.But Assignment entity has Proj_id as part of primary key.

Is Proj_id in Assignments entity came due to relation with tasks and resource entity.

Regards
Umashankar




Originally posted by hmscott
I may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott

database design question

Hi,
I've got the following problem. I've got 2-3 tables where I am keeping
information about a particular entity. Say tables A, B, C. Table A contains
the most important details about the entity, like id, name, address etc.
Tables B, C contain the id and supplementary information. I think I would
like to have at least the name on all 3 tables, ok maybe 2 of them, as the
name is very important and don't want to keep joining with the main table A
to find the name.
I do know this is not perfect practise in terms of database design. What's
the best way to maintain the names on tables B, C. A Trigger is the first
thing that springs to mind. Or are you against maintaining the names in 2-3
tables and I should instead stick to one table as my instict says!! What do
you think? Thank you.
Panos.Panos
Can you post the definition of the tables and relationship between them?
And can you elaborate a little bit what are you trying to achive?
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:8F222DF4-AB7E-425E-AE8C-BF5F7D25C55E@.microsoft.com...
> Hi,
> I've got the following problem. I've got 2-3 tables where I am keeping
> information about a particular entity. Say tables A, B, C. Table A
> contains
> the most important details about the entity, like id, name, address etc.
> Tables B, C contain the id and supplementary information. I think I would
> like to have at least the name on all 3 tables, ok maybe 2 of them, as the
> name is very important and don't want to keep joining with the main table
> A
> to find the name.
> I do know this is not perfect practise in terms of database design. What's
> the best way to maintain the names on tables B, C. A Trigger is the first
> thing that springs to mind. Or are you against maintaining the names in
> 2-3
> tables and I should instead stick to one table as my instict says!! What
> do
> you think? Thank you.
> Panos.|||If tableA is clustered indexed on ID, then you are better off than keeping
the name in just table A and joining (performance wise) than using triggers
or any other option to maintain the data integrity.
Hope this helps.
--
"Panos Stavroulis." wrote:

> Hi,
> I've got the following problem. I've got 2-3 tables where I am keeping
> information about a particular entity. Say tables A, B, C. Table A contain
s
> the most important details about the entity, like id, name, address etc.
> Tables B, C contain the id and supplementary information. I think I would
> like to have at least the name on all 3 tables, ok maybe 2 of them, as the
> name is very important and don't want to keep joining with the main table
A
> to find the name.
> I do know this is not perfect practise in terms of database design. What's
> the best way to maintain the names on tables B, C. A Trigger is the first
> thing that springs to mind. Or are you against maintaining the names in 2-
3
> tables and I should instead stick to one table as my instict says!! What d
o
> you think? Thank you.
> Panos.|||Hi,
Table A
^^^^^
col_id
entity_name
entity_type_id
short_name
alternative_name
source_unique_id
ext_ref_type_id
country_code
Source_id
record_date
Valid_from
Valid_to
event
next
prev
is_deleted
Delete_Reason
Table B
^^^^^^
col_id
prosp_name
entity_name
pairred
ref_entity_red
is_preferred
notes
jurisdiction
Entity_key
entity_type
depth
entity_form
industry_sector
industry_group
industry_subgroup
is_interesting
Yes the there is a clustered index on the col_id to answer your question. I
don't think it's too bad to join, it's just convenient to keep another name.
I think probably best if I don't maintain this duplication, I didn't like it
in the first place and have only one name on the main table A. Any other
opinions? Thanks.
Panos.
"Uri Dimant" wrote:

> Panos
> Can you post the definition of the tables and relationship between them?
> And can you elaborate a little bit what are you trying to achive?
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:8F222DF4-AB7E-425E-AE8C-BF5F7D25C55E@.microsoft.com...
>
>|||Panos
If I understood you properly you need something like that
SELECT A.entity_name,B.entity_name FROM TableA A JOIN TableB B
ON A.colid=B.colid
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:204DBCBB-6131-4A6D-B594-3AFFE1733A51@.microsoft.com...
> Hi,
> Table A
> ^^^^^
> col_id
> entity_name
> entity_type_id
> short_name
> alternative_name
> source_unique_id
> ext_ref_type_id
> country_code
> Source_id
> record_date
> Valid_from
> Valid_to
> event
> next
> prev
> is_deleted
> Delete_Reason
> Table B
> ^^^^^^
> col_id
> prosp_name
> entity_name
> pairred
> ref_entity_red
> is_preferred
> notes
> jurisdiction
> Entity_key
> entity_type
> depth
> entity_form
> industry_sector
> industry_group
> industry_subgroup
> is_interesting
> Yes the there is a clustered index on the col_id to answer your question.
> I
> don't think it's too bad to join, it's just convenient to keep another
> name.
> I think probably best if I don't maintain this duplication, I didn't like
> it
> in the first place and have only one name on the main table A. Any other
> opinions? Thanks.
> Panos.
> "Uri Dimant" wrote:
>|||Well I don't think my problem was how to link 2 tables together! That was
more of a design issue. Anyway, I think I've made up my mind what's the best
strategy.
"Uri Dimant" wrote:

> Panos
> If I understood you properly you need something like that
> SELECT A.entity_name,B.entity_name FROM TableA A JOIN TableB B
> ON A.colid=B.colid
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:204DBCBB-6131-4A6D-B594-3AFFE1733A51@.microsoft.com...
>
>|||Well ,design issue?
Take a look at
http://www.databaseanswers.com/data_models/index.htm -- examples
database design

"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:F38B0F4E-2A79-4948-B695-68D3F2E0D63C@.microsoft.com...
> Well I don't think my problem was how to link 2 tables together! That was
> more of a design issue. Anyway, I think I've made up my mind what's the
> best
> strategy.
> "Uri Dimant" wrote:
>|||Panos,
instead of denormalization, consider using indexed views and/or
covering indexes. You will get about the same performance for selects,
and you won't need to worry about data integrity.

Database Design

Can anyone tell me how can i design database architecture for the Table Category & Product...so that i can make N-level entity relation...

I have database in SQL SERVER 2000.

CategoryId
CategoryName
ParentId

Here ParentId is CategoryId. for root category it is null

productId
ProductName
CategoryId

|||

Visit below thread.

http://forums.asp.net/t/1162732.aspx