Hi,Im trying to port a dialog system i used to use in an old web game into a new SQL server DB from MySQL... although looking at the tables im just wondering if its worth me just putting it all into 1 table...Here is a rough table design:dialogs (table)id - int (autoincrement PK)text - textlink_type - bytelink - intdialog_options (table)id - int (autoincrement PK)text - textparent_dialog = intlink_type - bytelink - int
Now each dialog can lead to many things, be it another dialog, an event, a set of responses etc. So in the above example i used to have 2 tables one for dialogs and one for options, and if its link type wasnt either of these 2 then it was up to the code to run a script or something...SELECT dialogs.*FROM dialogsWHERE dialogs.id = @ID;
Anyway if you look at the 2 table designs they both pretty much share an IDENTICAL layout other than the options need to know the parent_dialog (which is the id in the dialog table it links to) as each dialog could have many dialog_options...SELECT dialog_options.id, dialog_options.text, dialog_options.link_type, dialog_options.linkFROM dialog_optionsWHERE dialog_options.parent_dialog = @ID;
So im really just wondering if it is best to keep these as 2 separate tables or should i just add the column parent_dialog into the dialogs_table then just have them both in the same table...SELECT dialogs.id, dialogs.text, dialogs.link_type, dialogs.linkFROM dialogsWHERE dialogs.parent_dialog = @ID;
I wanted to keep them separate originally as i thought for performance lookups would be faster on the dialogs (As there would not be as many rows in that table), and i would always need to do another query afterwards once the dialog is returned to get the options if there are any...So what are your professional opinions, am i over worrying and putting them all in one table for the sake of an extra int per row would keep similar data together, or should i keep them seperate even though the tables are near identical...I could always take like a hybrid approach and make a shared dialogs table that contained the shared data then have a smaller table that contained option specific stuff, but this would mean it would always need a left join to see what type the current dialog is, as currently i can distinguish if its a dialog or an option based on its originating table...
Which is better in this scenario
Posted - 2009-11-26 : 03:46:59
