Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would recommend using Data Normalization (<a href="http://www.bkent.net/Doc/simple5.htm" rel="nofollow">http://www.bkent.net/Doc/simple5.htm</a>). if you are keeping tags in string formats like "tag a, tag b tag c, etc" then you have to select every tag in the DB break them in to arrays and do matching on that in order to find the relavant data.</p> <p>I would recommend making a table for the Items and another for Tags that will grow as new tags are added then a third table mapping the item_id to the tag. Once this is done you can select based off specific tags and groups of tags to get results.</p> <pre><code>|Item_table | |-------------------------| |item_id | Desc | |1 | Event | |2 | Ticket Booking | |-------------------------| |Tag_Table | |------------| |id | tag | |1 | Rugby | |2 | Blue | |Tag_Map | |-------------------------| |id | tag id | item id | |1 | 1 | 1 | |2 | 1 | 2 | </code></pre> <p>You would create an index in the map table and the tag table. and you would use this like so:</p> <pre><code>SELECT it.Desc FROM Item_Table it, Tag_Table tt, Tag_Map tm WHERE tt.id = tm.tag_id AND it.item_id = tm.item_id and (tt.tag = "Rugby" or tt.tag = "Blue"); </code></pre> <p>you would only have to change the last part in () to one tag or keep adding "or" and another tag, that will give you any and all item descriptions that match any given tag.</p> <p>I know 3 tables looks like a lot to manage but trust me, if you item table and tag table begin to grow to the tens of thousands you (and you mysql server) will appreciate this style of data storage.</p>
    singulars
    1. This table or related slice is empty.
    1. POSimplified SQL search
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USWrenbjor
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSimplified SQL search
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COthis is what i first came up with, but we have to let clients enter tags. then we'll have to break these tags and insert each tag as a single record as well as check to see if there are duplicates. webbandit solution seems rather straight forward. take the list of tags per item, build a sql with 'or Like%%'. BUT this will happen on every page load so im not sure what the impact on the mysql server will be.
      singulars
      1. PO
      1. USheshanh
    2. COThe LIKE comparison will get very taxing on the MySQL server as the table size increases. The actual chalenge you face is scale. Youare asking MySQL to do a String search on a field. This is a "heavy" operation also you now have to search EVERY row in the table for each tag. if you have 1 million rows and search 3 tags thats 3 million rows of processing you have done. If you use my approach it will use Indexing to eliminate the rows it does not need and drastically reduce query time. If you only have 500 rows, no biggy use LIKE. but once you scale over time you will wish you used normal forms.
      singulars
      1. PO
      1. USWrenbjor
    3. COI see your point. after looking at the database we have about 300-600 rows. I dont think it will ever reach million with the biggest dataset we might endup with 1000 rows but its very unlikely. But for future expansions this seems the best approach. Ill have a chat to the rest of the dev team and see how they want to go about. Thanks for the info :)
      singulars
      1. PO
      1. USheshanh
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload