1

First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex topic it couldn't have maybe 3 - 4 possible solutions and I hope it's okay here.

My basic convention singular/plural of table names is as follows:

  • products - plural
  • product_variants - mixed - variants of product

This convention worked great, but I got a problem right here:

  • I have a products table (yes, I am naming them in plural. Do you think it's bad? Give me arguments why, please)
  • I have categories_products table - (I also have categories for ingredients table, that's why it isn't named just categories)
    • I didn't want to name it category_products, because it may sound like n:1 products of category
  • I have product_categories table, which stores n:n of products AND categories_products

I tried the same with singular:

  • product
  • category_product - sounds like n:n table
  • product_category - sounds like n:n again

Or

  • product
  • product_category - would be list of categories then
  • product2product_category - n:n table

Sound the most clean

But since it's a complex application and I don't want to change all the tables to singular if not necessary, I tried even plural

  • products
  • product_categories
  • products2product_categories

But I don't like that one.

Is there any better way to do such naming, without making observer, or even me, confused?

The very similar question is about enum definition of some dataset, without confusion

  • products table have product_status_id - so it's more dynamic than enum
  • table product_statuses - sounds like 1:n table - statuses of product

Can somebody turn me into the right direction, and explain why is the direction best?

Martin.
  • 111
  • 3
  • You want an "SQL style guide" - I like this one - except that author accepts plural names - I don't (one exception - orders, where I can't avoid using the word! The key (pardon the pun :-) ) is to pick one style and stick to it! – Vérace Mar 18 '20 at 04:22
  • I kinda don't like the article "Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services" - kinda would make even more confusion. – Martin. Mar 19 '20 at 00:11
  • I agree with you - as far as I'm concerned, the only logical name for a joining table (or Associative Entity is a combination of the names of the two tables being joined - another caveat! – Vérace Mar 19 '20 at 00:13
  • Do you have a clue what would be a best for such n:n table if I have the last table in this chain created from "categories_products" ? – Martin. Mar 19 '20 at 00:32
  • I would have category_product - what's the problem you have with what you propose, if your SQL coding style guide/standard allows plural table names? – Vérace Mar 19 '20 at 01:12
  • and then product_category as n:n? how would you know what is what? – Martin. Mar 19 '20 at 01:16
  • I tend to do it alphabetically - I mean, there's no point in having category_product and product_category - since they would contain the same data only with the first and second fields reversed - CREATE TABLE category_product (category_id INT NOT NULL, product_id INT NOT NULL); Then add the relevant PK constraint (i.e. both fields together) and the FKs - pointing back to the respective PKs of category and product. What else can one do? – Vérace Mar 19 '20 at 01:21
  • that's exactly the problem, I have categories for ingredients AND for products, which makes me unable to use "category" as base, "product" as base, and "product_category" as n:n – Martin. Mar 19 '20 at 03:18
  • Maybe then a naming scheme which has t1_t2_2_t3 (or t1_t2_to_t3) or similar? That way, the convention clearly shows that you have a combo of tables on one side and a single one on the other! Are you saying that you need to link 3 tables in this way? – Vérace Mar 19 '20 at 03:31

0 Answers0