CODEWITHSUNDEEP
mysqldatabaseforeign-keysrelational-database
dziwna
dziwna

Reputation: 1242

How to create relation between single column from one table and multiple rows from second table?

I have two tables: Players and Characters. Every Player can have max. 10 Characters. I'm looking for best way to create relation between those tables. I was planning to use Foreign Key to connect column characters in Players with id from Characters, but I don't know if I should create 10 columns in Players like character1, character2, character3, etc. for each his character?

I know I can create column which will have value of type integer[], but what about relation then?

I hope my question is quite clear, because I don't know how could I explain it better.

Upvotes: 2

Views: 490

Answers (2)

Ian Wood
Ian Wood

Reputation: 6573

Your foreign key should be on the characters table - referencing the player_id. There is no way to restrict this to 10 in the constraint - you will have to either code for this of do a check in a procedure call to createCharacter which will perform the check for you.

Upvotes: 3

MikkoP
MikkoP

Reputation: 646

You should definitely create two tables, one for Players and one for Characters. The Players table will use either a separate player_id column or a player_name column as the primary key. If you choose to have a separate player_id column, make sure you set the player_name as unique. The Characters table will have a character_id column, a player_id or a player_name column (depending on how Players is structured) which is a foreign key reference to the Players table and finally a character_name column. Each character will now represent its own row in the Characters table.

Upvotes: 3

Related Questions