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?

  • 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 aka hot2use Aug 30 '17 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 '17 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 aka hot2use Aug 30 '17 at 8:47
  • Just the id.... – erv Aug 30 '17 at 10:17
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.

|improve this answer|||||
  • Yeah, that's pretty much the structure I ended up using. Cheers. – erv Aug 30 '17 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;
|improve this answer|||||
  • 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 '17 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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