Best practice for SQL table names when using sub namespaces that exceed maximum table name length
I have a MySQL database that includes many tables named according to a namespace that uses sub names and underscores (
a_b_c), such as the following:
...some of the namespace names exceed the database's maximum table name length (e.g., 64 characters). The namespace itself is clear to me and would be sufficiently practical, if it were not for the database table name limit.
How to deal with a namespace that exceeds 64-characters and a database that has a maximum 64-character table name limit? The following options come to mind...
- Extend the database's table name length limit. However, this does not seem practical, possible, and/or good practice. Otherwise, this would be a nice solution, because I would be able to continue using my current namespace.
- Shorten the names by using only the sub-name, such as
kingdom. However, this creates ambiguity with other naming constructs in the same namespace (e.g.,
classmay be confused with
life_domain_kingdom_phylum_class) and may intersect at some level.
- Shorten the names by using only the first few characters of each sub name. For example, rename
lif_dom_kin_phy_cla_ord_fam_gen_spe. However, the shortened names are difficult to comprehend.
- Create a limited length, unique hash for each namespace name, then use an external tool to map the hashed names to the actual/desired/full namespace names, then name the tables using the hashed names and refer to them using the external tool. For example, the namespace name
life_domain_kingdom_phylum_classmaps to the table name
asd8fjkd8f, and the namespace name
life_domain_kingdom_phylum_class_ordermaps to the table name
ifuel9kf03l. Although this option seems to depend on an external mapping tool, it seems to be the most flexible/practical.
I read the following posts, but they seem to discuss styling conventions rather than database table name length limitations:
Based on comments received, I realize I maybe should have used more examples than the biological tables (e.g.,
The database includes other groups of tables, some of which are for storing one-to-many or many-to-many relationships, such as the following:
carriann last edited by
There is essentially no reason to prefix tables with the database they are in. Ditto for prefixing column names with the table names. Keep in mind that these syntaxes can give you an equal amount of clutter, without threatening the max name lengths:
genus_species; I suggest that is sufficient for that table.