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?
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.