0

This may be a bad structure. Let me know if it is, it's been a long time since I did any database design.

I have a tblGame in a mySQL database, which has two fields:

ID_Table
ID_Player

In the actual game, multiple players will sit on the one table. So I have both fields set as a combined primary key. And ID_Table is set to auto-increment. ID_Player is an auto-increment in another table (tblPlayers). What I'd like to do is to get the ID_Table value before any players are added to the table. The actual game analogy is to an open table (with ID_Table already assigned) sitting there waiting for players to join. As they join, they get added to tblGame.

Is it possible to do this?

4
  • 1
    Why not just create a table tblTable with the columns ID (autoincrement and description? You then link tblTable and tblPlayers to tblGame. No need to block the tblGame with irrelevant data.
    – John K. N.
    Aug 30, 2017 at 6:14
  • I could do that. Then the 'description' column becomes irrelevant in that case (it's not needed). Is it ok practice to have a table that consists of solely an auto-id field?
    – erv
    Aug 30, 2017 at 6:45
  • Yes, that is possible. But how to you reference the table if you have no description? Say you have two tables. Will the tables be just referenced with their ID in the game, or will they have names?
    – John K. N.
    Aug 30, 2017 at 8:47
  • Just the id....
    – erv
    Aug 30, 2017 at 10:17

2 Answers 2

2

You could implement the following solution.

Game "Table"

This table stores the auto-generated values of the (playing) Tables for the game:

CREATE TABLE tblTable (
    ID int NOT NULL AUTO_INCREMENT);

Game "Players"

This table contains all the relevant Player data:

CREATE TABLE tblPlayer (
    ID         int          NOT NULL AUTO_INCREMENT,
    LastName   varchar(244) NOT NULL,
    FirstName  varchar(244) NOT NULL,
    Age        int          NOT NULL,
    PRIMARY KEY (ID)
);

Game "Games" (Matches)

This table links the tblPlayer table with the tblTable data.

CREATE TABLE tblGame (
    ID_Table   int,
    ID_Player  int
);

Creating a new table

When you create a new table in your game you just initialize a new ID by setting off the following statement:

INSERT INTO tblTable (ID) VALUE DEFAULT;

This will create a new value (ID). You would have to store this value in your program code to use when assigning players to the table.

1
  • Yeah, that's pretty much the structure I ended up using. Cheers.
    – erv
    Aug 30, 2017 at 10:19
-2

If you are using Mysql Database this query can help you

CREATE TABLE Your_table_name (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;
1
  • Yeah, I can create the tables fine. It was more a question of structure and extracting the value. So from your answer, can I assume that it's fine to create a table that only has the one field (auto incrementing ID) in it? Cheers.
    – erv
    Aug 30, 2017 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.