Why Shouldn't Commas Be Included In Sql Field Names?

People keep telling me that spaces shouldn't be included in column names. I was just wondering, why is that? It is an issue I am having with a few database tables I am creating for

Solution 1:

tldr; Trying to included commas (or other special characters) generally indicates a fundamental flaw with the column name.

This is a bad database design. It is bad because it tries to encode information into a column name. This is not the point of a column name! A column name is merely a friendly moniker for an element of a record/tuple - nothing more. Under Relational Algebra (RA), and thus SQL, it is the record/tuple that contains the information.

Besides just leading to a schema that is hard to deal with (extra quoting syntax) that requires hard-coding queries based on changing information (column names and multiplicity), it is also impossible to use with a number of RA techniques in a flexible manner. RA can only generally handle multiplicity across records - and, as discovered, this includes joins.

Instead, the schema should look similar to, say:

County    State   Other Columns
=======   =====   =============
Darke     OH      ..
Prebel    OH      ..

Where the Key is, say, (State,County) and "Other Columns" are dependent upon the key. Of course the model should be correctly normalized in relation to all the other information that is captured.

Note that there is no information stored in the column names presented above: the names are merely friendly monikers representing the information stored in each column.

---

Now a PIVOT transformation, which is primarily for human output/display, can be performed as needed. This is were a column in the output table (not schema table!) is generated per set of record values. However, this is a secondary issue and should not affect the primary schema.

If using SQL Server, one could first UNPIVOT the information-filled column names, perform the join and then re-PIVOT (given a known set of column names). However, I have no idea how this would be done in MySQL - a messy dynamic query, perhaps. In any case, this is an approach I would avoid.

---

While special characters are not allowed in bare identifiers, it is possible to use such names when used in quoted identifiers - e.g. `Vancouver, WA` or, with ANSI quotes, "Vancouver, WA". However, keep reading the rest of this response which argues against using such identifiers.