What is a good rule-of-thumb for naming link-tables?

In the same way that a publication table might relate to a person table via subscriptions, or a company table might relate to a person table via employee, I’m wondering if there is a descriptive way to relate a company table to a company_type table.

Here are some rough (and simplified) examples of the relevant tables.
company:
– |id|name|
company_type:
– |type|description|
{name needed}:
– |id|company_id|type|

Also, I realize that not all relationships can be as succinctly named as subscriptions or employee, so if that’s the case here, what would be a good rule of thumb to avoid a near name-collision with something like company_types?

Additional Details:
Company types, in our current case, are somewhat convoluted. This industry has multiple supply channels and multiple customer channels, so while “vendor” is a valid company_type, a vendor can also be “independent”, “authorized”, or “franchised” … or any mix of the three.
Customer types are very similarly multi-faceted, and to further compound the issue, a single company can simultaneously be of some vendor and customer types.

You seem to have one to many relationship between company and company_type. One company can be of multiple types? Perhaps you could give the set of types for a given company a name, “profile”, “classification”.

company_profile or company_classification

Alternatively you could describe the relationship e.g:

company_link_company_type

Questions of these kind can occur when you have no clear naming convention for your link tables. But for a database schema of a certain size, it really helps to have such conventions, especially when you have more than one person working on that schema. Moreover, you should have some other, more general naming conventions, like when to use singular and when plural, how to name your primary keys and your foreign keys etc.

For link tables, this may result in “technical” names name like the suggestion of @Joppe, or in a name like company_company_type or company_to_company_type. But when you communicate that convention within your organisation, this should not become much of a problem. Just make sure you stick to that convention once you made the decision.

Labeling things is easy. Naming things is hard. So many times, we rely on adjacent terminology or unspoken assumptions to provide a technical distinction without providing a clear, categorical distinction. A quote from an unrelated context exemplifies this quite well:

The following note has made a deal of fun in London: ‘Dear Sir: How
comes it that I have had no proofs of Love from you since last
Saturday? I have waited with the utmost impatience.’ Signed, Charlotte
Burry.

But the fun vanishes when the reader learns that Lady Charlotte Burry
had a novel entitled Love in press, and that the note was to her
printer.

– Kazlitt Arvine, The Cyclopaedia of Anecdotes of Literature and the Fine Arts, 1853

The source of the post is from Futility Closet

Tautology

Among varied forms and applications, tautology refers to redundant usage: red blood, kind of type, type of class. There is no clarification by using that particular descriptor.

Taxonomy

Taxonomy is a scheme that includes a set of structured rules for describing things. This scheme might be oriented towards relationships (parent-child, specialization-generalization, inheritance-composition), but it can be organized around other discernment strategies. The earlier in the rule set, the less specific the rule, with the later rules providing distinction in what would otherwise be a subtle difference. It describes a process for classification. A model might is the embodiment of a taxonomy.

Categorization

This is the process of labeling the whole of something in order to count or measure it. The subject satisfies a degree of similarity, and lacks any disqualifying characteristic that we can call it the same.

Characterization

These are the individual features or traits of a thing. This may include qualities of personality or behavior. Or they may be physical properties.

To the problem at hand, to quote Uncle Bob’s Clean Code:

The name of a variable, function, or class, should answer all the big
questions. It should tell you why it exists, what it does, and how it
is used. If a name requires a comment, then the name does not reveal
its intent.

Martin, Robert C. (2008-08-01). Clean Code: A Handbook of Agile Software Craftsmanship (p. 18). Pearson Education (USA). Kindle Edition.

What we have before us is a mental map (pg 25 of Clean Code). The solution specifies something that is probably a bit different from what it actually represents in the living domain.

To remedy this, we need to further characterize the things before applying a category to them. What are the behaviors, relationships, or properties of the things? Do not describe it in terms of the database or design as it sits, as it is already contributing to the difficulty in clarity, but how they would describe themselves in terms of interactions, or the specific activities they perform?

This is what is clear:

Company: a business involved in the industry that participates at
times as a supplier and as a customer
Supplier: provides something to a customer; a vendor is synonymous; each supplier may or may not have an affiliation with another supplier
Supplier Model: how a supplier is organized, affiliated, and
operates; examples include independent, authorized, franchised
Supplier Affiliation: explicit supplier relationship describing two suppliers who are organized, affiliated, or operate in cooperation with each other; each affiliation will have a supplier model describing the nature of the relationship
Customer: buyer of a good or service or a privilege from a supplier
Customer Model: ????
Customer Affiliation: ???
Purchase: a record of a sale between a supplier and a customer
Sellable Good: the resource or item distributed and sold by a supplier and purchased by a customer
Sellable Service: an activity performed by a supplier for a customer
Sellable Privilege: granting a legal opportunity to a customer to use something or perform an activity that is normally restricted by legal right of the supplier

Filed under: softwareengineering - @ 04:37

Thẻ: architecture, database, naming, orm, research

Thiết kế website giá rẻ

Danh mục

What is a good rule-of-thumb for naming link-tables?