not sure if this would be consider "on topic" or not, but i have a design
question for all the brains out there.

problem:  many-to-many ( with priority ).

"Person" can speak many "Languages"
Languages can be spoken by any number of "Persons"
People have an order of preference that they speak their languages.

Fred speaks, English, Pig Latin, and Spanish.. ( in order of preference )
Barnery speaks Spanish, Pig Latin, and English  ( in order )
Wilma  speaks Spanish, English, and Pig Latin  ( in oder )

How would you design a set of tables and a relationship too be able to tell
that Fred and Barney should speak "Pig Latin" when talking to each other.
and Wilma and Fred should speak English, and Barney and Wilma should speak
Spanish

Any input would be appreciated.

Matt

As an example of many-to-many relationship you can allok at Northwind
database

There are three (actually more ) table Orders [Order Details] ,Products

So, you can order many products within OrderId as well as Prodyct may have
many Orders.
MS done it by using  a "junction" table called [Order Details] which
containsd OrderId ,ProductId and some other columns

I'm sure you'll get an idea.






Show quoteHide quote

1.Person Table
PersonID int
PersonName nvarchar(255)
.......

2.Language Table
LanguageID int
Language nvarchar(255)

3.PersonLanguagePreference  Table
PersonID int
LanguageID int
PreferenceOrder smallint

perfect, thanks

Show quoteHide quote

It would a n-m relationship,something like this here(simplified)

Table user
(
        UserID,
        UserName
)

Table Languages
(
        LanguageID,
        languageName
)

Table Userlanguages
(
        UserID,
        LanguageId,
        Preferenceorder
)


HTH, Jens Suessmeyer.

perfect, thanks

Show quoteHide quote

RDBMS takes more work than you have been told in these "quickie
newsgroup answers".  Get in the habit of doing it right fromthe start,
or you might was well be using a word processor to store data.

1) Do not forget to add constraints and RI in the schema.
2) Remember to do your research for ISO and other standards.

Something more like this:

CREATE TABLE Persons -- need a standard here!
(person_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE  LanguageCodes -- ISO 639
(language_code CHAR(3) NOT NULL PRIMARY KEY,
language_name CHAR(15) NOT NULL,
..);

CREATE TABLE  Speakers
(language_code CHAR(3) NOT NULL
      REFERENCES LanguageCodes (language_code)
     ON UPDATE CASCADE
     ON DELETE  CASCADE,
person_id INTEGER NOT NULL
     REFERENCES Persons (person_id),
     ON UPDATE CASCADE
     ON DELETE  CASCADE,
PRIMARY KEY (language_code, person_id),
language_rank INTEGER DEFAULT 1 NOT NULL
    CHECK (language_rank > 0),
  UNIQUE (language_code, person_id, language_rank),
...);

The job is not done yet.  You now need to write short procedures to add
new speakers, to re-arrange the rankings  and to keep gaps out of the
language rankings.

Help with this trigger
Sum Total Hours and Minutes in a report
Aggregate Function Error
SQL query
Joining two tables multiple times