Many to Many to Many

In my DB I am working on, I have 4 many-to-many relationships.
However, I now need to join two of those together to create a
many-to-many-to-many relationship.
Is this possible? I have tried what i thought was correct (creating another
join table) and got erroneous results to say the least.
better to post ddl or give more real details
>--Original Message--
>In my DB I am working on, I have 4 many-to-many
relationships.
>However, I now need to join two of those together to
create a
>many-to-many-to-many relationship.
>Is this possible? I have tried what i thought was
correct (creating another
>join table) and got erroneous results to say the least.
>
>.
>
sql

Many to Many to Many

In my DB I am working on, I have 4 many-to-many relationships.
However, I now need to join two of those together to create a
many-to-many-to-many relationship.
Is this possible? I have tried what i thought was correct (creating another
join table) and got erroneous results to say the least.better to post ddl or give more real details
>--Original Message--
>In my DB I am working on, I have 4 many-to-many
relationships.
>However, I now need to join two of those together to
create a
>many-to-many-to-many relationship.
>Is this possible? I have tried what i thought was
correct (creating another
>join table) and got erroneous results to say the least.
>
>.
>

Many to Many Relationships Fact->Attribute->Leaf in AS2005

I have a dimension with two natural hierarchies climbing up from the leaf.
One hierarchy is quite deep, and is the one I'm interested in. The other
hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
table joins to the simple attribute hierarchy, but I want to analyse using
the deeper one.
There is in effect a many to many relationship between the fact table and
the leaves of the dimension. This information can be implied because we're
descending a natural hierarchy to get from where the fact table joins to the
leaf. Leaf to Attribute is a Many to One relationship, so Attribute to Leaf
is One to Many and Fact to Attribute is Many to One. Many-One-Many =
Many-Many.
What I'd like is for Analysis Services to realise that and give me Many to
Many navigation when I try to analyse the facts using the other hierarchy.
Instead the analysis seems to fail, and I just get the grand total in all
fields at all levels when drilling down the other hierarchy.
To make things work, I'm having to set up the Many-Many relationship
explicitely by creating a Measure Group using the dimension table, along wit
h
two Dimensions (one containing just the joining attribute, the other the rea
l
leaf, the joining attribute and the deep hierarchy). The result is as
expected with correct summations, but it is not as neat a solution as just
having AS work this out from the single dimension.
I notice that attribute relationships in the February CTP version have two
properties - Cardinality and Relationship Type. I can't find any
documentation on these, but wonder if they'd help. I've been treating every
relationship as strict - so if I know a value for an attribute I can always
resolve a single value for any member properties of that attribute. Attribut
e
to Member Property is a Many to One relationship.
As an aside, this introduces subtelty as it means I must be always sure I
can uniquely identify an attribute by its key - a risk where an attribute is
identified by name and the name can appear in multiple places with different
meaning in different parts of the natural hierarchy (two towns in the UK
called Leeds for example). I am currently working around this by using each
parent level as part of the composite key so "Yorkshire, Leeds" is different
to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at the
moment no other way to do this based on the basic principle that an attribut
e
must be uniquely identified in order to allow AS to uniquely identify its
member properties and satisfy the Many-One relationship.
Thanks
- RichardIf there is a many-to-many from the fact table to the leaf level; then you
need to create an intermediate junction table which represents that M:M
relationship. You create a measure group for that M:M junction table -- and
establish relationships from there. See the AdventureWorksDW for an example.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:F952BDFC-CFBC-49C2-BA86-64BAD15F4F47@.microsoft.com...
> I have a dimension with two natural hierarchies climbing up from the leaf.
> One hierarchy is quite deep, and is the one I'm interested in. The other
> hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
> table joins to the simple attribute hierarchy, but I want to analyse using
> the deeper one.
> There is in effect a many to many relationship between the fact table and
> the leaves of the dimension. This information can be implied because we're
> descending a natural hierarchy to get from where the fact table joins to
the
> leaf. Leaf to Attribute is a Many to One relationship, so Attribute to
Leaf
> is One to Many and Fact to Attribute is Many to One. Many-One-Many =
> Many-Many.
> What I'd like is for Analysis Services to realise that and give me Many to
> Many navigation when I try to analyse the facts using the other hierarchy.
> Instead the analysis seems to fail, and I just get the grand total in all
> fields at all levels when drilling down the other hierarchy.
> To make things work, I'm having to set up the Many-Many relationship
> explicitely by creating a Measure Group using the dimension table, along
with
> two Dimensions (one containing just the joining attribute, the other the
real
> leaf, the joining attribute and the deep hierarchy). The result is as
> expected with correct summations, but it is not as neat a solution as just
> having AS work this out from the single dimension.
> I notice that attribute relationships in the February CTP version have two
> properties - Cardinality and Relationship Type. I can't find any
> documentation on these, but wonder if they'd help. I've been treating
every
> relationship as strict - so if I know a value for an attribute I can
always
> resolve a single value for any member properties of that attribute.
Attribute
> to Member Property is a Many to One relationship.
> As an aside, this introduces subtelty as it means I must be always sure I
> can uniquely identify an attribute by its key - a risk where an attribute
is
> identified by name and the name can appear in multiple places with
different
> meaning in different parts of the natural hierarchy (two towns in the UK
> called Leeds for example). I am currently working around this by using
each
> parent level as part of the composite key so "Yorkshire, Leeds" is
different
> to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at
the
> moment no other way to do this based on the basic principle that an
attribute
> must be uniquely identified in order to allow AS to uniquely identify its
> member properties and satisfy the Many-One relationship.
> Thanks
> - Richard|||> If there is a many-to-many from the fact table to the leaf level; then you[vbcol=seagreen]
> need to create an intermediate junction table which represents that M:M
> relationship. You create a measure group for that M:M junction table -- an
d
> establish relationships from there. See the AdventureWorksDW for an example.[/vbco
l]
I have this working thanks. I've hidden the new measure (a count measure on
the join) so as not to confuse the end user.
While'st here - could you explain the Cardinality and Type
(strict/otherwise) properties on the Attribute Relationships and check that
my understanding of having to ensure an attribute key uniquely identifies an
instance of that attribute is correct. I'm working on the basis that it can'
t
infer that two instances of an attribute with the same key are different
because they appear at different points in a hierarchy or have different
member properties. AS2000 could do this but its model was completely
different.
Thanks
- Richard|||With SQK2K5, you *must* have a unique key for every attribute. If it isn't
unique, then you need to make it unique. For example, if you have a Time
dimension with Quarters and Months, then in SQL2K you could have the keys
be:
Month: 1 to 12
Quarter: 1 to 4
And tell the system that member keys were not unique across the dimension.
In this case, the system automatically used the hierarchy to create an
internal unique key by walking up the hierarchy.
With SQL2K5, attributes are first-class citizens and as such they must have
a unique key across the entire dimension. This means that it is now your job
to create a unique concatenated key. In the case above, you need to click on
the key field and you will see an option to create a key "collection" --
i.e. what we call a concatenated key. So now your attribute keys need to be:
Month: year + 1 through 12
Quarter: year + 1 through 4
If you don't do this, then the dimension won't be rendered properly and
members will disappear or appear under the wrong parent.
This is a frequent issue that new users (particular SQL2K users coming over
to SQL2K5) run into. Most new folks know what a concatenated key is -- and
just take it as it was relational terms. The folks who struggle with this is
those of us coming over from SQL2K :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:5BC8D21F-D8E4-4D94-A597-1544A1F24793@.microsoft.com...
you[vbcol=seagreen]
and[vbcol=seagreen]
example.[vbcol=seagreen]
> I have this working thanks. I've hidden the new measure (a count measure
on
> the join) so as not to confuse the end user.
> While'st here - could you explain the Cardinality and Type
> (strict/otherwise) properties on the Attribute Relationships and check
that
> my understanding of having to ensure an attribute key uniquely identifies
an
> instance of that attribute is correct. I'm working on the basis that it
can't
> infer that two instances of an attribute with the same key are different
> because they appear at different points in a hierarchy or have different
> member properties. AS2000 could do this but its model was completely
> different.
> Thanks
> - Richardsql

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to many & other relationships

Can someone help me sort something out. Suppose we have two tables in a database.
One is named Person, and one is named Birthday. Is this a many to many relationship
or a 1 to many relationship?

Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

My first question would be:

why are you creating a table just for birthdays, when you can just add a field to the Person table?

|||

This is just a hypothetical question. Probably not the best one in the world but one I believe will help me to understand. So if someone can help me out I would appreciate it.

|||

Hi,

these articles will help you get familiar with Entity Rlationship Model

http://en.wikipedia.org/wiki/Many-to-many

http://en.wikipedia.org/wiki/Entity-Relationship_Model

I hope this helps.

|||

A hypothetical question or a philosophical one? Well, those wikipedia articles are a bit dry so let me explain with a different example.

ASP.Net 2.0 membership gives you the concepts of a User (person) and a Role. A User can be in many Roles - the same login can be assigned to the Administrator role, the Sales role, the Helpdesk role etc. A Role can have many users. This is a many-to-many relationship.

To model this, we have a Users table and a Roles table. And key to the many-to-many relationship is a table "in the middle", containing the User ID and the Role ID. This allows the same User to be in the connecting table many times with different roles. And the same role to be in the connecting table with many Users.

Hope that helps!

|||

If we are going to talk about database design, then your database has to be normalized (Idea: google it).

The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.

But if you still beileve for the example you wrote in the last line:

AppDevForMe:

Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.

You have to create a new table (namedAccessor table in database world), which will take the primary key of each of the two tables ... those primary keys will be forign key in the newly created table (the accessor table) and they will form the priamey key for it (it called a composite primary key in database world).

Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).

I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).

Hope this will help you a alot.

|||

If we are going to talk about database design, then your database has to be normalized (Idea: google it).

The fast solution which is also normalized is to have a column in the "Users" table for the BirthDay.

But if you still beileve for the example you wrote in the last line:

AppDevForMe:

Person has many birthdays. Example. Joe Schmoe has a birthday for every year of his life.

birthday(7/11/1976) has many People (Many people have a birthday on 7/11/1976.

If we supposed this is the case (which is a very bad design), then the relation is Many-to-Many and here is how to deal with this relation.

You have to create a new table (namedAccessor table in database world), which will take the primary key of each of the two tables ... those primary keys will be forign key in the newly created table (the accessor table) and they will form the priamey key for it (it called a composite primary key in database world).

Now, the relation become One-to-Many for each of the old two tables with the newly created table (Accessor).

I suggest you to read about: ERD, database design, database relations, normalization (must), denormalization (good some time to increase the perfomance by making number of joining with tables less).

Hope this will help you a alot.

sql

Managing PK/FK Relationships with the Tools

Ah, why doesn't SQL Server Management Studio (SSMS) or Visual Studio's Server Explorer permit me to set PK/FK constraints on the tables? They're supported in SQL Everywhere (SQL Ev), but I don't see a way to set them up.

Why don't these tools support scripting the database tables to SQL? Is this planned?

Bill,

Obviously Microsoft will have to address the why, but in terms of the how, there is a nice third party tool that might help you out. See www.primeworks.pt.

You are correct though, the degree to which you can manage constraints on SQL Mobile databases in SQL Server 2005 Management Studio is limited unless you write the DDL yourself. VS2005 is even more limited.

Darren

|||I took a quick look at the primeworks utilities. Yes, I think that's probably the best answer. Thankfully, the third-party community can step in where MS falls short. Hopefully the "Data Dude" (I hate that name) code will help, but it's gross overkill for most SQLCE developers--it's like sending a patient to an ICU for a stubbed toe.