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!
Best Answer
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