jimmyoneshot Posted April 16, 2011 Share Posted April 16, 2011 I'm wondering what is the best practice in the following situation. I have a site which users can use to upload both videos and photos to albums Currently my database has 3 tables - objects, albums and albums_objects. The type of object is defined using a field in the objects table called object_type which contains an integer 1 = video 2 = photo The type of album is defined using a field in the albums table called album_type which contains an integer 1 = video album 2 = photo album The albums_objects table is a join table and simply contains object_id and album_id indicating what objects are inside specific albums. I'm basically wondering have I gone about this in the right way or should I be creating more tables i.e. the following tables:- videos photos photo_ablums video_albums videos_video_albums photos_photo_albums Which is the most efficient method? My currently existing one or having several tables? Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 16, 2011 Share Posted April 16, 2011 Hi I would say how you have it now is the best way to do it. It is best to avoid duplicating tables. All the best Keith Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted April 16, 2011 Author Share Posted April 16, 2011 Cheers Keith. Heres another thing I'm thinking about. I also have a users table that has the following fields:- user_id, email, password, first_name, surname, phone However my user's can either be regular users or managers but the last 3 fields listed above ONLY apply to managers meaning if a regular user registers then the last 3 fields in that row would be blank for that user. Would this example need to be split into 2 tables or could I just select only the fields that apply to the type of user being viewed on my front end and perhaps add a user_type field? Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 16, 2011 Share Posted April 16, 2011 Hi Best practice might well be to split them off. However not sure I would bother depending on the ratio of users to managers. All the best Keith Quote Link to comment Share on other sites More sharing options...
jimmyoneshot Posted April 16, 2011 Author Share Posted April 16, 2011 Aha got ya. I think it makes sense to make use of a type field in this case because it's more adaptable for the future if more user types need to be added otherwise I'd have to add a new table for every type of user and point my queries at all sorts of different tables. Thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.