Hans123 Hans123 - 1 year ago 60
SQL Question

MySQL table structure with one row. Best handling for management and insertion

I'm just creating a social network for practising my skills (PHP, HTML, CSS, JavaScript, etc.).
Now when designing the database layout a question appears, that I'm unhappily not able to solve.

I have a table called UserMain:

| Field | Type |
| u_id | bigint(20) unsigned |
| u_email | varchar(256) |
| u_password | varchar(30) |
| u_data | varchar(25) |
| u_friends | varchar(28) |

For storing the general data, that's being input when registering.

I wanted to separate the users data (prename, surename, sex, birthday, etc.) into another table called data and of course the relationship between users in a table called friends. So I decided to create a data- and friends-table for every user, via php using the u_id above and I came up with something like this, [u_id]_data:

| Field | Type |
| u_prename | varchar(20) |
| u_surname | varchar(20) |
| u_sex | boolean |
| u_birthday | DATE |
| u_avatar | varchar(28) |

Now I don't want to attach value at the friends table, because the problem starts obviously with the [u_id]_data table. A user just has one pre- and surname, etc., so it is a 1-row-table. Now the question:

How do I handle the input of the table in relation to the primary key?

For me, creating a new "id int not null auto_increment pk" seems needless for a single row, so I don't know what combination of columns to use for the primary key.

Maybe you know better implementations of this design, but please consider the following:

It doesn't matter what new implementation you have, the only thing I don't want to have is a table called
in which I have the data of all users.

Alright, I maybe have a bad opinion about MySQL or I'm not really good informed, but my idea of just having multiple data-tables comes from performance reasons.

My idea when changing or inserting data:

  1. GetTheUsersId (Searching User-Table for the Id. That could take a
    littlebit if I would have ... let's say 10,000,000 users)

  2. When having the [u_id] I just can use the data-table to find what I'm searching for.

With a table that is made up of (again) 10,000,000 rows it would take longer. Now don't start laughing as I'm taking the abstraction and dimensions to a level far away. It's just for supporting the idea of saving performance.

Answer Source

the only thing I don't want to have is a table called data in which I have the data of all users.

Please tell us more about why this is not an option? It's a perfectly valid way to store user data.

But to answer your question, you probably don't need any key at all if there is only one row in a table. You are going to refer to the row by the table name in most cases anyways:

SELECT * FROM [uid]_data ...
SELECT * FROM [uid]_data JOIN ...
UPDATE [uid]_data ...
INSERT INTO [uid]_data ...
DELETE FROM [uid]_data --You're probably going to want to DROP the table as well
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download