MySQL table design

All we need is an easy explanation of the problem, so here it is.

I’d like to have a ‘records’ table (lets say this table is populated with some values I’ve created before-hand)

I also want a user to be able to create his own records (And only he will be able to watch his records but he will also be able to watch all the ‘General’ records I’ve created before-hand).

Given that, What would be a more appropriate table design and why?

1.

CREATE TABLE records (
record_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
user_id int,
name VARCHAR,
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`));

This way all the records I’ve inserted myself will have null in the user_id foreign key. and all the records created by actual users will have their user_id
2.

CREATE TABLE records (
record_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR);

CREATE TABLE user_records(
user_id int NOT NULL,
record_id int NOT NULL,
PRIMARY KEY(user_id, record_id));

This way all the general records will be in the records table and all the user_records will be separated.

What’s considered to be a more useful/reasonable design? For example I believe the 2nd design will be a bit easier to scale (I can add more columns without worrying if their purpose will fit the foods table or not.

Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I would assign a user_id that would represent an "admin" entry, and go with the first approach. That way you can add more records later and not have to worry too much about it, since the system will handle it just like any other user insert. It has the additional benefit of not allowing NULLS at all. In general I try to avoid using NULL as a meaningful value for anything other than "No Information Present"

As far as allowing users to see what you put in, the code being used to retrieve their records would just have an OR clause in it. ex:

SELECT [Record_Info] FROM user_records

WHERE [email protected]_id OR user_id=9999

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply