多对多relation? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

多对多relation?

多对多relation?# Database - 数据库

m*e2000-10-18 07:10

1 楼

I want to know what is the best method to implement a many-to-many
ralation in database.
For example, if I am writing a database application about 棋谱, I will
have a table called 'players' to store the players' ID and name. I will
have another table called 'games' which stored the real 棋谱. Because
a player can play many games and a game must have two players, this is
a 2-to-many ralation.
I think it is very inconvenient to use a intermediate table to implement
such relation. But if I use only 2 t

u*y2000-10-18 07:10

2 楼

why don't you add two columns player1 and player2 to the qi
pu table?

【在 m*****e 的大作中提到】

: I want to know what is the best method to implement a many-to-many
: ralation in database.
: For example, if I am writing a database application about 棋谱, I will
: have a table called 'players' to store the players' ID and name. I will
: have another table called 'games' which stored the real 棋谱. Because
: a player can play many games and a game must have two players, this is
: a 2-to-many ralation.
: I think it is very inconvenient to use a intermediate table to implement
: such relation. But if I use only 2 t

m*e2000-10-18 07:10

3 楼

I have two ways to do this
(1)use only 1 table called Games
which has following fields:
Game_ID as primory key
Black_Name
White_Name to store 2 players
other fields....
This is simple but the table is not normilized well.
If I later want to find all the players in my database,
I have to use a whole table search to find it.
(2)use two tables: Games and Players
Players table has a Player_ID and a Player_Name
Games table has a Game_ID, two IDs to store the two players
This des

【在 u*******y 的大作中提到】

: why don't you add two columns player1 and player2 to the qi
: pu table?

u*y2000-10-18 07:10

4 楼

player and game table are necessary. If you want to search a
game based on a player's name, then denormalize game table
with the players' name. And maybe you need to do more
denormalization to do other kind of search.
Denormalization is good here. Your game table does not have
much update operation I think.

【在 m*****e 的大作中提到】

: I have two ways to do this
: (1)use only 1 table called Games
: which has following fields:
: Game_ID as primory key
: Black_Name
: White_Name to store 2 players
: other fields....
: This is simple but the table is not normilized well.
: If I later want to find all the players in my database,
: I have to use a whole table search to find it.

s*g2000-10-18 07:10

5 楼

What about having three tables?
PLAYER(pid, pname) pid is the key
GAME(gid, other attributes) gid is the key
GAMR-PLAYER(gid,pid) composite key of gid and pid.
Then they are well normalized. Can someone tell me how the performance
will be if we have the above three tables?

【在 m*****e 的大作中提到】

a*o2000-10-18 07:10

6 楼

That's the only way to make it normalized. But to achieve
better performance, you may denormalize it to some extent.
For example, add player name to game-player table.

【在 s*****g 的大作中提到】

: What about having three tables?
: PLAYER(pid, pname) pid is the key
: GAME(gid, other attributes) gid is the key
: GAMR-PLAYER(gid,pid) composite key of gid and pid.
: Then they are well normalized. Can someone tell me how the performance
: will be if we have the above three tables?

s*g2000-10-18 07:10

7 楼

Thanks!
What do you think about having the GAME and GAME-PLAYER rather than just one
table containing all the attributes related to game?

【在 a****o 的大作中提到】

: That's the only way to make it normalized. But to achieve
: better performance, you may denormalize it to some extent.
: For example, add player name to game-player table.

a*o2000-10-18 07:10

8 楼

Mmm. Maybe I'm wrong. GAME-PLAYER could be not necessary.
actually gameid functionally determines the players
attending it and other attributes. So having game and player
table are already normalized. To divide game into two tables
may make sense if the attributes of a game other than
players are rarely used. This way most queries that are only
concerned about the players of a game will be accelerated
since a block will hold more records. This is called
vertical partition. But this totally depen

【在 s*****g 的大作中提到】

: Thanks!
: What do you think about having the GAME and GAME-PLAYER rather than just one
: table containing all the attributes related to game?

s*g2000-10-18 07:10

9 楼

Yeah, I think u r right, except the functional dependency part.
Actually each game involves two players, so for the tables having
both GameID and PlayerID, the key must consists of GameID and PlayerID.
If the tables have other attributes of game, then it is not normalized.
Since there exists partial dependency.

【在 a****o 的大作中提到】

: Mmm. Maybe I'm wrong. GAME-PLAYER could be not necessary.
: actually gameid functionally determines the players
: attending it and other attributes. So having game and player
: table are already normalized. To divide game into two tables
: may make sense if the attributes of a game other than
: players are rarely used. This way most queries that are only
: concerned about the players of a game will be accelerated
: since a block will hold more records. This is called
: vertical partition. But this totally depen

a*o2000-10-18 07:10

10 楼

No. GameID should uniquely determine a game in its name. the
two player ids are determined by the gameid, so are other
attributes of this game.

【在 s*****g 的大作中提到】

: Yeah, I think u r right, except the functional dependency part.
: Actually each game involves two players, so for the tables having
: both GameID and PlayerID, the key must consists of GameID and PlayerID.
: If the tables have other attributes of game, then it is not normalized.
: Since there exists partial dependency.

s*g2000-10-18 07:10

11 楼

What you said is true if we have the table haveing gameid, playerid1,
playerid2, and other attributes. Gameid itself can be the key for this
case.
Let's think about the table GAME(gameid, playerid, other attributes).
Gameid itself can't uniquely identify each record, so the key should be
consisted of gameid and playerid.
Well, maybe I misunderstand you in a certain way.:)

【在 a****o 的大作中提到】

: No. GameID should uniquely determine a game in its name. the
: two player ids are determined by the gameid, so are other
: attributes of this game.

a*o2000-10-18 07:10

12 楼

gameid is not an ID of game? then why call it "gameid"?

【在 s*****g 的大作中提到】

: What you said is true if we have the table haveing gameid, playerid1,
: playerid2, and other attributes. Gameid itself can be the key for this
: case.
: Let's think about the table GAME(gameid, playerid, other attributes).
: Gameid itself can't uniquely identify each record, so the key should be
: consisted of gameid and playerid.
: Well, maybe I misunderstand you in a certain way.:)

s*g2000-10-18 07:10

13 楼

Sure it is the id of game, but for each game, there are two players.
If we have two columns for playerID, then gameid can uniquely identify
each record. But if the table has only one column for playerid, then
it seems to me we should have an extra table specifically for gameid and
playerid.
I guess I didn't make myself understood, sorry about it.

【在 a****o 的大作中提到】

: gameid is not an ID of game? then why call it "gameid"?

p*s2000-10-18 07:10

14 楼

there is no definition of "well-normalized" in database. :)
it is necessary to enumerate all functional dependencies, then it is
possible to judge whether the schema is in 3NF or BCNF.

【在 s*****g 的大作中提到】

p*s2000-10-18 07:10

15 楼

2-to-many doesn't make sense here.
The so-called 1-to-many (1-to-1, many-to-many, respectively) means
1 ROW/TUPLE in left entity is associated with many ROWS/TUPLES in right entity,
not 1 COLUMN/ATTIBUTE to many COLUMNS/ATTRIBUTES.
Your case is two COLUMNS/ATTRIBUTES in a table referring to another table.
This is not 2-to-many relationship in ER model. This is just 2 foreign key
attributes in relational model.
CREATE TABLE player
( player_id NUMBER NOT NULL,
player_name VARCHAR(100),
PRI

【在 m*****e 的大作中提到】