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 (_ 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:

    • https://dba.stackexchange.com/questions/262159/how-to-properly-name-a-database-table
    • https://dba.stackexchange.com/questions/250023/best-name-to-name-a-table
    • https://stackoverflow.com/questions/3593582/database-naming-conventions-by-microsoft
    • https://dba.stackexchange.com/questions/5588/is-there-a-reason-to-use-extremely-abbreviated-table-names?rq=1

    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...


  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2