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.