Storing List in Mysql

Question

I want to store user information in Mysql for my Python Program. One of the things I want to store is username history (static list) Another is which groups they are a member of (dynamic list)

I am new to storing data in Mysql so am trying to figure out the best structure to achieve this. It seems like I could create 1 table for each user and have the name hsitory as a column, but everything I read tells me this would be wasteful and inefficient.

so for example....

Table = users

user_ID | current_username | username_history | groups_joined | groups_banned
========|==================|==================|===============|==============
01567   | Dave             |Michael,Geoff,    |group1,group2, |group4,group5
        |                  |Bob,Nigel,Colin   |group2         |group5,group7
========|==================|==================|===============|==============
01568   | Fred             |Martin,Simon,     |group3,group4, |group4,group3
        |                  |Leo,Nick,Arthur   |group6         |group2,group12

My first thought was to do something like the above and when I have a list to store like username_history I would convert the list to a string with comma seperated values and store it in a LONGTEXT field as shown. Then to add usernames as the user changes them I could use concat to add to the string.

This would work I guess, but it feels ugly and im sure there must be a better way. Also I think this would be very inefficient if I needed to search for a name in username history, find out all users that were called Fred for example.

My next thought was to create an entire table per user and populate the username_column with one name per field. But googling around I found similar questions from database noobs all with replies saying this would create thousands of tables and be very inefficient.

ok so now im looking at relational tables (correct terminology??)....

table = username_history

user_id | usernames
========|==========
01567   | but I still need a list here....

I'm sure this is a very common problem for beginners, but I just can't seem to get my head around how the structure for my usage would look.

Thanks to anyone taking the time to help and advise :)

Are you bound to MySQL? Most non-relational databases have a list type, like MongoDB. — hewiefreeman, Jul 15 '19 at 22:31
No I'm not, I am new to database usage for my own applications. I have only been a database user when installing other peoples software. I'm not aware of different types. If mongoDB supports lists then maybe thats what I need — ProgD, Jul 15 '19 at 23:03
There is absolutely no need to change database so that you can store lists. In relational databases lists are stored in a separate table (one table for all users, not a table per user). — slaakso, Jul 16 '19 at 08:49
@slaakso so from what I'm reading I would have a table named Username_History with 2 columns... user_ID and usernames. Then each user will have an additional row for every username they have used. So to get username history I would search the ID column for every entry matching the users ID — ProgD, Jul 16 '19 at 12:24
@ProgD Yes. See the answer below. You have a table for each data entity. Each row in users represent different user. Likewise each row in username_history shows an username for each user (one-to-many relationship). The tables are linked together with user_id column (users.user_id= username_history.user_id). What you might add is a numeric key for the row, so if user has same username multiple times, you can tell the instancies apart. Likewise, you might want to add a datetime column showing when the user created the username. — slaakso, Jul 16 '19 at 15:11

score 1 · Accepted Answer · answered Jul 15 '19 at 22:35

Create a table username_history with:

create table username_history (
id int not null auto_increment,
user_id varchar(10),
username varchar(30),
created_at datetime default CURRENT_TIMESTAMP,
primary key (`id`),
index (user_id)
);

You can then get the usernames in list format using GROUP_CONCAT-function.

score 1 · Answer 2 · answered Jul 15 '19 at 22:49

You really need three tables:

Groups
Users
UserGroups

The first contains a list of the groups which user may join. It could be as simple as a unique ID and Group Name.

The second table is similar to your table in the question. For this purpose, the only columns we are concerned with are the unique ID for each user and their user name.

The third table has two columns: user_id and group_id. When a user joins a group, a row is inserted into this table with the unique IDs of the user and group.

You could also have a column with a timestamp of when the row was added, and a column for the user's status if needed.

The timestamp column would let you know when the user joined the group.

The status column could indicate if the user is banned from the group, or if the user left the group.

Storing List in Mysql

2 Answers2