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? Link to comment https://forums.phpfreaks.com/topic/233908-best-practice-multiple-tables-and-join-tables-or-type-field/ 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 Link to comment https://forums.phpfreaks.com/topic/233908-best-practice-multiple-tables-and-join-tables-or-type-field/#findComment-1202357 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? Link to comment https://forums.phpfreaks.com/topic/233908-best-practice-multiple-tables-and-join-tables-or-type-field/#findComment-1202360 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 Link to comment https://forums.phpfreaks.com/topic/233908-best-practice-multiple-tables-and-join-tables-or-type-field/#findComment-1202375 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. Link to comment https://forums.phpfreaks.com/topic/233908-best-practice-multiple-tables-and-join-tables-or-type-field/#findComment-1202385 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.