Design Question

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.
"Matt" <marblesack@.removethis.gmail.com> wrote in message
news:uGJL8AgAGHA.2704@.TK2MSFTNGP15.phx.gbl...
> 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.
>
>
>
>
>
>|||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|||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
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1134719361.920183.10590@.g44g2000cwa.googlegroups.com...
> 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
"SQL novice" <balacr@.gmail.com> wrote in message
news:1134715717.836635.241100@.g49g2000cwa.googlegroups.com...
> 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
>|||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.