Databases
StackOverflow2013
Postsdbo
POGUID vs Identity for use with tags
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGUID vs Identity for use with tags
    primarykey
    data
    text
    <p>I know this subject has been discussed at length (I have read lots of posts here and all over the web) and I don't like to flog a dead horse but I have a question about a more specific aspect of Integer ID vs GUID:</p> <p>I am writing out a schema which is going to have a tagging ability similar in use to the stackoverflow tags but it will have the same tags used against 5+ different tables.</p> <p>The basic tables I will be linking are as follows:</p> <p><strong>Tag Table</strong></p> <pre><code>Tag ID Tag Name Tag Description ------------------------------------------------------------- 1 Hats Tag for hats 2 Coats Tag for coats 3 Gloves Gloves tag 4 Ladies Ladies item </code></pre> <p><strong>Items Table 1</strong></p> <pre><code>Item ID Item Name Cost ------------------------------------------------------------ 1 Deerstalker £20.00 2 Fedora £50.00 3 Scarf £15.00 </code></pre> <p>The bit I'm having trouble with is the <code>tag_item</code> table.</p> <p>I will have 5 tables with completely different structures that I want the users to be able to apply tags to so I think I'll need to do one of the following:</p> <ul> <li>Store <code>table name</code>/<code>table number</code> as well as the integer key of the row the tag relates to</li> <li>Store a <code>GUID</code> of the row, this will work independent of the table and make it much easier to get all tags for a certain row.</li> </ul> <p>What I'm unsure of is how this will affect performance for:</p> <ul> <li>Searching for all items over 5 tables with a certain tag/tags</li> <li>editing the tags for an item</li> <li>joins</li> </ul> <p>Is there any clearly better option in this case or anywhere I could read up on the advantages in this particular scenario? </p> <hr> <p><strong>Edit:</strong></p> <p>For anyone interested, below is the schema I have decided upon:</p> <p>Table for Tags</p> <pre><code>CREATE TABLE [dbo].[Sys_TagList]( [Sys_Tag_Primary] [int] IDENTITY(1,1) NOT NULL, [Sys_Tag_Name] [varchar](50) NOT NULL, [Sys_Tag_Description] [varchar](1000) NULL ) </code></pre> <p>Table for Tag_Items</p> <pre><code>CREATE TABLE [dbo].[Meta_Tags]( [Met_Tag_Primary] [int] IDENTITY(1,1) NOT NULL, [Met_Tag_Link_FK] [int] NOT NULL, -- Link to [Sys_Tag_Primary] field [Met_Tag_Name] [varchar](25) NOT NULL, -- To enable tag searches to use a single table [Met_Tag_Table] [varchar](25) NOT NULL, -- Table name, used with [Met_Tag_RowID] to create link to that item [Met_Tag_RowID] [int] NOT NULL -- Primary key of item[n] table ) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbendataclear
    1. USbendataclear
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGUID vs Identity for use with tags
      1. USbendataclear
      1. VTBountyStart
    2. VO
      singulars
      1. POGUID vs Identity for use with tags
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POGUID vs Identity for use with tags
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO"*Store a GUID of the row, this will ... make it much easier to get all tags for a certain row.*" No, it really will not. Just store two columns, the TableName (or a TypeID that identifies the table) and the RowID. GUIDs will not help with this problem the way that you think they will, they are just going to make things more difficult, complex and confusing. Virtually everyone does it this way (a table identifier and a row identifier).
      singulars
      1. POGUID vs Identity for use with tags
      1. USRBarryYoung
    2. CO@RBarryYoung, can you explain your reasons as to why `GUID` is not a valid solution?
      singulars
      1. POGUID vs Identity for use with tags
      1. USbendataclear
    3. COIt's perfectly valid, it just not easier and likely to be harder. It boils down to two things: 1) GUID are an unusual datatype that is slightly clumsier to handle on both the client and server than the usual key types (integers and strings), and 2) having to search all possible tables to figure out which one a GUID belongs to is not only slower, it is a gigantic Pain-In-The-Ankle code-wise. The is especially true for SQL queries/code, because it ***so*** much wants to know it's column set ahead of time.
      singulars
      1. POGUID vs Identity for use with tags
      1. USRBarryYoung
 

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