0

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., class may be confused with school_x_program_x_class or 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 life_domain_kingdom_phylum_class_order_family_genus_species to 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_class maps to the table name asd8fjkd8f, and the namespace name life_domain_kingdom_phylum_class_order maps 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:

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...
8
  • Surely those who are writing queries for this database are familiar with the Linnean system and biology in general? The life_ bit appears to be redundant. Have d_k_p as an abbreviation for domain_kindom_phylum - that should give you all the space you need? I would suggest that this is a better solution than having tables name ifuel9kf03l! Jan 20 at 6:51
  • 2
    What's wrong with just phylum , class order, etc.? You have FKs that enforce the relationship. I can see a grouping (by different life groups) like worldbuilder_stackexchange_genus vs real_species Jan 20 at 10:00
  • 2
    As pointed out by Michael, you should name a table after the direct object(s) it's data represents, regardless of where that object lives in the hierarchy of entities. Your table life_domain_kingdom_phylum_class_order_family_genus_species doesn't directly store all of the lifes, nor all of the kingdoms, nor all of the phylums, etc. It directly stored species so it should just be called species and it's foreign key relationship to it's parent entity genus (another table) should exist and explicitly define the hierarchy.
    – J.D.
    Jan 20 at 13:11
  • 1
    You may be interested in the public taxonomy data from itis.gov which stores all those entities in one table named hierarchy. Jan 20 at 13:24
  • 1
    lh_domain (life hierarchy domain) is a parent to lh_kingdom which in turn is parent to lh_phylum - chose as many letters as you need for lh - even the full life_hierarchy_xyz... would do it - class, order... and so on down the line? No? Jan 20 at 15:49

1 Answer 1

0

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:

dbname.tablename

tablename.columnname

"Everybody" recognizes genus_species; I suggest that is sufficient for that table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.