mailing list members format

Got a simple example for the many to many relations?


"Joe Fawcett" wrote in message

>
> "Andy B" wrote in message
>
>>I am making an announcement only mailing list service for the company
>>website. I am trying to figure out if I should make a single table for the
>>lists and then create an xml column to keep all of the list members or if
>>I should do something different. Any ideas?
>>
>>
> Unless you have a particular not to I would just do this in a traditional
> fashion. A table for lists, one for members and one that links the two as
> one member can join many lists and one list can have many members.
> I see no advantage in using XML in this instance and it will mean more
> work constructing queries to update membership of lists etc.
>
> --
>
> Joe Fawcett (MVP - XML)
>
> http://joe.fawcett.name
>
>
 >> Stay informed about: mailing list members format 

Well if your table of lists has a ListId (int) as its primary key and your
Member table has a MemberId (int) as its primary key then you have two basic
choices for the linking table:

ListId int
MemberId int

where the two fields, which reference the List ands Member tables make a
composite primary key or

ListMemberId int
ListId int
MemberId int

where the first field in the primary key, possibly an identity field, so
auto-populated.

You can then use this table to join the others and produce a resultset that
shows all lists and all members.

--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name

"Andy B" wrote in message

> Got a simple example for the many to many relations?
>
>
> "Joe Fawcett" wrote in message
>
>>
>> "Andy B" wrote in message
>>
>>>I am making an announcement only mailing list service for the company
>>>website. I am trying to figure out if I should make a single table for
>>>the lists and then create an xml column to keep all of the list members
>>>or if I should do something different. Any ideas?
>>>
>>>
>> Unless you have a particular not to I would just do this in a traditional
>> fashion. A table for lists, one for members and one that links the two as
>> one member can join many lists and one list can have many members.
>> I see no advantage in using XML in this instance and it will mean more
>> work constructing queries to update membership of lists etc.
>>
>> --
>>
>> Joe Fawcett (MVP - XML)
>>
>> http://joe.fawcett.name
>>
>>
>
>
 >> Stay informed about: mailing list members format