0

I have the following database table. I am trying to figure out a way that I can structure this so that I can have a position for each player column. Because each user is going to have multiple players and there will be multiple users, I cannot figure out the best way to model my db table for efficiency. 

CREATE TABLE `user_players` (

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player1` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player3` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player4` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player5` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `player6` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

The only thing that I can think of is adding a player_position for ever player, so that it would look like this...

`player1` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`player_position1` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`player2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`player_position2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

Is there a better, more efficient way to do this?

Becky
  • 2,283
  • 2
  • 23
  • 50

2 Answers2

3

You need separate tables for users and players. The player table will have a foreign key for the user that owns it.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • So something like keeping my user_id in the players table. I have a users table now, so you are saying to connect the two tables for this? The approach I mentioned with my positions, is that the most efficient way to do it? – Becky Jul 18 '15 at 05:03
1

If you want to design efficient databases, then I'd suggest you to first get atleast some knowledge about Normalization.

To learn basics of Normalization, refer to:

Clearly your database is not Normalized and needs Normalization.

Issue 1: Achieve 1st Normalization form by assigning a Primary Key.

Issue 2: Your database consists of Transitive Dependency(Transitive dependency if you consider id as a primary key. Thereafter, player fields will depend upon non key attribute. i.e. user_id).

  • Fix it by creating different tables for user and player.
  • Also take a look at the concept of Foreign Key.

If you fix these two issues then you'll no longer need both id and user_id together. You can drop one of them.

Final Database Schema:

CREATE TABLE `user` (
    `user_id`   int(11) NOT NULL PRIMARY KEY, /*Make it AUTO_INCREMENT if you wish to*/
    `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `lastname`  varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `username`  varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `email`     varchar(100) COLLATE utf8_unicode_ci NOT NULL
)

CREATE TABLE `player` (
    `player_id` int(11) NOT NULL PRIMARY KEY, /*Make it AUTO_INCREMENT if you wish to*/
    `player1` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `player2` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `player3` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `player4` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `player5` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `player6` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
)

P.S.: Syntax may vary depending upon the type of database that you're using.

Community
  • 1
  • 1
Raman Sahasi
  • 30,180
  • 9
  • 58
  • 71
  • I need the id for this table because I need it ordered the way it is inserted into the db – Becky Jul 18 '15 at 15:41
  • You asked for efficient databases and I'll give you suggestion as per the question. I'd still recommend you to use only one, i.e., either `user_id` or `id`. However, if you want them to be ordered the way they're inserted into the database then you can make use of SQL Date, Time, Timestamps. (Refer: http://www.w3schools.com/sql/sql_dates.asp) This way you can also fetch them according to the time when they were inserted (i.e., ordered) into database and you'll have additional info. But still if you want to go that way, you can go. I don't think that it will create any significant difference :) – Raman Sahasi Jul 18 '15 at 16:01
  • Right, I know I didn't say that in my original question. I am inserting all of the data at one with a php shuffle to randomize the order and that is why I need the ai id field. You definitely gave me some insight. Thanks! – Becky Jul 18 '15 at 17:11