2

I'm pretty new to database design. I'm storing records which are tagged with some (few) of a (large) number of possible tags (much like StackExchange questions). At the moment I have a single table with one record per row and one column per tag, very simple.

But then I read that "a well-normalized database will never need more than 100 columns in a table", which clashes with my approach of simply adding a new column every time a new type of tag is created.

I guess that the alternative is to have one table per tag, and to JOIN when I want to work out which tags a particular record has. When I create a new tag I'd make a new table rather than a new column.

At what point should I normalise? Clearly if I have millions of possible tags, and each record has only ~10 tags selected, then I'm wasting space by storing the sparse columns. Is there another disadvantage to continually adding columns to a simple table?

Is there an objective answer to this? Does it depend on frequency of new tag-type creation? Or sparsity? "100 columns" seems like a very arbitrary limit.

If it makes a difference I'm using SQLite.

3
  • 2
    I think you need to consider a 3 table/one-to-many relationship. Have your base table with the record you are going to tag (with an PK/ID), a second table with an entry for each tag (with a PK/ID) and then a joining table that references the base table's PK/ID to the tag table's PK/ID.
    – Jeff A
    Commented Jun 16, 2017 at 22:37
  • 1
    Your table has one column per tag (i.e., the DBA.SE post table would have columns sql-server, postgresql, oracle, mysql, normalization, etc, and this post would have NULL for the first four, and 1 for the last)? Or, you have several types of tags, and each row can have one tag of that type (i.e., post would have a DBMS column which could be mysql, oracle, etc, but would be NULL for this post, and a DBTheory column which could have values like database-design, and for this post would have normalization)? or, something else? Trying to envision the set-up here.
    – RDFozz
    Commented Jun 16, 2017 at 22:59
  • @RDFozz At the moment the table has one column per tag, as you describe, but (if it makes a difference) I have 0/1 instead of NULL/1. In the longer-term we may want to group tags but we're not doing that right now.
    – Samizdis
    Commented Jun 17, 2017 at 9:58

0

Your Answer

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

Browse other questions tagged or ask your own question.