1

As I understand it, one way to 'abstract' a parent/child relationship in SQL would be to have a table set up with a joining foreign key table. Ie in this case, the swimsets are children of different style_tags:

swimset_styletag

id          swimset_id  styletag_id
----------  ----------  -----------
1           1           1          
2           2           2          
3           3           1          
4           3           2    

swimset

id          set_description        
----------  -----------------------
1           4 x 100 Free Interval A
2           4 x 100 Back Interval A
3           6 x 100 free/back on in

styletag

id          tag_name  
----------  ----------
1           Free      
2           back 

However, I'm testing the idea of just using Booleans and therefore one single table instead of the foreignkey:

swimset

id          set_description          free_tag  back_tag
----------  -----------------------  --------  --------
1           4 x 100 Free Interval A  True      False
2           4 x 100 Back Interval A  False     True
3           6 x 100 free/back on in  True      True

My understanding is that the queries would be faster (one table), and that it will still be easy enough to query all of the swimsets that are tagged "free" (ie look up all of the children of a certain parent). Are there any downsides that I'm missing here by lumping everything into one table?

2 Answers 2

0

One problem with using a boolean column for each tag is that you would be limited to two tags only. If you ever want to support tags for breaststroke, butterfly, sidestroke, doggy style, … those would all require schema changes. You would also have to alter your queries to support those columns. As a result, your application code won't support tags in general; each tag will feel like a new feature that you have to add code for.

(MySQL has no true boolean type. The closest approximation would be numeric types TINYINT(1) or BIT(1).)

0

I your case that second table (styletag) have only two rows and if this table would not have more rows in feature, you can use one table instead of three separated table. In general if you have a many-to-many relationship, it's better to use three table, two table for your entities and one table for build relationship between two table that have two foreign key to two entities. Assume styletag would be like this:

id          tag_name  
----------  ----------
1           Free      
2           back
3           breaststroke
4           butterfly

In this case you must have two more columns in swimset table and if one rows add to styletag, you must change swimset structure and add one more columns that is not efficient for big databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.