Ne0_Dev Posted May 16, 2011 Share Posted May 16, 2011 Hi All, I am after a bit of advice/suggestions on the following scenario: I have a site that has 3 tables in it's database: Videos Themes Customers At present both primary keys of the themes table and customers table are stored in the videos table as foreign keys. Each video can only be associated to 1 customer and 1 theme. My question is what would be the best way to introduce some separate categories to which a video can be associated to that is not classed as a theme. E.G. each video can only be associated to a single theme or category but not both. Would I need to add another table or can I use the existing ones and add an additional field to them? Any help gratefully received. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/ Share on other sites More sharing options...
fugix Posted May 16, 2011 Share Posted May 16, 2011 could make a table with category names as fields and insert each video in the correct field Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216269 Share on other sites More sharing options...
fenway Posted May 17, 2011 Share Posted May 17, 2011 could make a table with category names as fields and insert each video in the correct field Oh, please don't ever make categories field names!!! Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216287 Share on other sites More sharing options...
requinix Posted May 17, 2011 Share Posted May 17, 2011 I would simply keep (that is, add to the Videos table) two foreign key fields for theme and category and make sure my PHP code knows that only one can be used at a time. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216303 Share on other sites More sharing options...
fugix Posted May 17, 2011 Share Posted May 17, 2011 could make a table with category names as fields and insert each video in the correct field Oh, please don't ever make categories field names!!! why not fenway? Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216324 Share on other sites More sharing options...
Ne0_Dev Posted May 17, 2011 Author Share Posted May 17, 2011 Hi All, thanks for the suggestions. I have decided to keep it simple and take requinix advice and created a new table called categories and store the primary key in the video table. I have just one other question regarding the structure of a SQL query for this database. Would someone be able to give me an example of how to list all videos and output the correct theme or category next to it? I assume I will need to use a JOIN of some sort. Not sure if this is any help but I am setting the categoryid and themeid field in the video table to 0 depending on which of these 2 the video has been associated to. E.G If video is associated to a theme then the categoryid in the video table will set to 0 and vice versa. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216408 Share on other sites More sharing options...
ignace Posted May 17, 2011 Share Posted May 17, 2011 why not fenway? Because it goes against the entire principle of proper database design. Do you really think ALTER TABLE .. ADD COLUMN .. is a better alternative to a simple INSERT INTO .. VALUES ..? Not to mention the performance hit you'll take for choosing to use an ALTER TABLE approach since the entire table will lock every time you add a new 'category' leaving thousands of your users waiting for the table to be unlocked. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216417 Share on other sites More sharing options...
VZdemon Posted May 17, 2011 Share Posted May 17, 2011 you could just make a theme field in the videos table and a videos field in the themes table and then assign the correct theme to the proper video. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216578 Share on other sites More sharing options...
fugix Posted May 17, 2011 Share Posted May 17, 2011 why not fenway? Because it goes against the entire principle of proper database design. Do you really think ALTER TABLE .. ADD COLUMN .. is a better alternative to a simple INSERT INTO .. VALUES ..? Not to mention the performance hit you'll take for choosing to use an ALTER TABLE approach since the entire table will lock every time you add a new 'category' leaving thousands of your users waiting for the table to be unlocked. noooo that wasnt what i was suggesting at all that a terrible idea...sorry if I was misunderstood Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216652 Share on other sites More sharing options...
DavidAM Posted May 18, 2011 Share Posted May 18, 2011 I have just one other question regarding the structure of a SQL query for this database. Would someone be able to give me an example of how to list all videos and output the correct theme or category next to it? I assume I will need to use a JOIN of some sort. Not sure if this is any help but I am setting the categoryid and themeid field in the video table to 0 depending on which of these 2 the video has been associated to. E.G If video is associated to a theme then the categoryid in the video table will set to 0 and vice versa. You will have to use a couple of LEFT JOINS. SELECT whatever FROM Videos LEFT JOIN Themes ON Videos.ThemeID = Themes.ThemID LEFT JOIN Categories ON Videos.CategoryID = Categories.CategoryID By the way, in the database world, you should assign NULL to a column that is not being used (CategoryID or ThemeID) instead of zero. That would mean that you need to allow NULLS in the table definition. Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216806 Share on other sites More sharing options...
ignace Posted May 18, 2011 Share Posted May 18, 2011 noooo that wasnt what i was suggesting at all that a terrible idea...sorry if I was misunderstood Care to explain what you were then suggesting? Quote Link to comment https://forums.phpfreaks.com/topic/236542-database-design-question/#findComment-1216863 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.