0

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!

0

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 user_id=@user_id OR user_id=9999

2
  • Hi there! Thank you for your answer. Actually, I did thought of this solution but it made me think of some more vulenerabilities it has. for example now this admin is a complete user which means if for some reason someone might find out its credentials then he would be able to remove all the general records from the database. Also for a little debate, what if I'll need to add in the future some kind of system to determine whether or not a user record is a "trusted" record by other users. Then the first approach would be come "clustered" with unnecessary data. no?
    – Ofek
    Mar 25 at 20:04
  • Also just to clarify, you SELECT FROM records and not user_records as you were talking about the first approach
    – Ofek
    Mar 25 at 20:05

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.