I have a MySQL database that includes many tables named according to a namespace that uses sub names and underscores (_ as in a_b_c), such as the following:
- life_domain_kingdom_phylum
- life_domain_kingdom_phylum_class
- life_domain_kingdom_phylum_class_order
- life_domain_kingdom_phylum_class_order_family
- life_domain_kingdom_phylum_class_order_family_genus
- life_domain_kingdom_phylum_class_order_family_genus_species
- ...etc...
...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 withschool_x_program_x_classorlife_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
life_domain_kingdom_phylum_class_order_family_genus_speciestolif_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 nameasd8fjkd8f, and the namespace namelife_domain_kingdom_phylum_class_ordermaps to the table nameifuel9kf03l. 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:
- How to properly name a database table
- Best name to name a table
- https://stackoverflow.com/questions/3593582/database-naming-conventions-by-microsoft
- Is there a reason to use extremely abbreviated table names?
UPDATE 2022-01-20
Based on comments received, I realize I maybe should have used more examples than the biological tables (e.g., life_domain_kingdom_phylum_class...).
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:
- group
- product
- derivative
- lab_result
- group_x_product
- group_x_product_x_derivative
- group_x_product_x_derivative_x_lab_result
- ...etc...
life_bit appears to be redundant. Have d_k_p as an abbreviation fordomain_kindom_phylum- that should give you all the space you need? I would suggest that this is a better solution than having tables nameifuel9kf03l!phylum,classorder, etc.? You have FKs that enforce the relationship. I can see a grouping (by differentlifegroups) likeworldbuilder_stackexchange_genusvsreal_specieslife_domain_kingdom_phylum_class_order_family_genus_speciesdoesn't directly store all of thelifes,nor all of thekingdoms, nor all of thephylums, etc. It directly storedspeciesso it should just be calledspeciesand it's foreign key relationship to it's parent entitygenus(another table) should exist and explicitly define the hierarchy.hierarchy.lh_domain(life hierarchy domain) is a parent tolh_kingdomwhich in turn is parent tolh_phylum- chose as many letters as you need forlh- even the fulllife_hierarchy_xyz...would do it - class, order... and so on down the line? No?