Thread: Dusk #634

Originally Posted by hc747

Yikes. Why would you use username as the primary key? Use a numeric PK with username as an (unique) attribute of the player table. Something akin to this would be much better imo.

Code:

create table if not exists dusk.item_container
(
    id int auto_increment
        primary key,
    type varchar(50) not null,
    constraint item_container_type_uindex
        unique (type)
);


create table if not exists dusk.player
(
    id int auto_increment
        primary key,
    username varchar(12) not null,
    constraint player_username_uindex
        unique (username)
);


create table if not exists dusk.player_item_container
(
    player_id int not null,
    item_container_id int not null,
    tab int default 0 not null,
    slot int not null,
    item_id int not null,
    item_amount int not null,
    primary key (player_id, item_container_id, tab, slot),
    constraint player_item_container_item_container_id_fk
        foreign key (item_container_id) references dusk.item_container (id),
    constraint player_item_container_player_id_fk
        foreign key (player_id) references dusk.player (id)
);


create index player_item_container_item_id_index
    on dusk.player_item_container (item_id);

Regardless, really like this project. Best of luck.

That was the initial concept, it's been reworked since then.
Thanks for the valuable feedback, it goes miles.
rep++