1

I am modifying existing code to create a view, and was looking for commonly accepted set of style guidelines on how to rename the resulting columns in the view when the component table column names clash.

Perhaps you have written one yourself. Please share your guidelines.

CREATE OR REPLACE VIEW libraries_metadata AS
SELECT
    l.id AS libraries_id
    , l.description AS libraries_description
    , l.name AS libraries_name
    , lg.description AS library_groups_description
    , lg.name AS library_groups_name
FROM
    libraries l
    INNER JOIN library_groups lg ON
    l.library_group_id = lg.id
    ;

Note: I could not quickly find any guidelines on this here:
Are there any published coding style guidelines for SQL?

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47

3 Answers3

2

The style I see used most often is tablename_columnname, but that is so much a matter of taste and arbitration that I have never seen anybody publish a style guide for that. For example, if the view is called customer_details, the table customer_data and the column id, many people might go for the less redundant and shorter data_id.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you! Just to confirm, `libraries.name` from table `libraries` should be renamed to `libraries_name`, **not** `library_name` according to this convention. Is this correct? – Timur Shtatland Oct 29 '21 at 14:05
  • 2
    As I said, there is no convention. Use whatever you like best – Laurenz Albe Oct 29 '21 at 14:11
  • 2
    @TimurShtatland, I name my tables in plural form, i.e. `libraries`, not `library`, because the table contains information about many libraries. When I look at columns, I'm looking at attributes of a specific library, so the column names are in singular form, i.e. `library_name`. Also, I think, it is closer to natural English. Same, as you have in the question `library_group_id`, not `libraries_group_id`, not `library_groups_id`. In the end, consistency is more important, so pick some style and follow it everywhere. But, if you pick an awkward style people would tend to not stick to it. – Vladimir Baranov Nov 03 '21 at 10:59
2

There is really no guide for this, but I would go with something like this

CREATE OR REPLACE VIEW libraries_metadata AS
SELECT
    l.id AS lib_id
    , l.name AS lib_name 
    , l.description AS lib_desc
    , lg.name AS lib_group_name
    , lg.description AS lib_group_desc
FROM
    libraries l
    INNER JOIN library_groups lg ON
    l.library_group_id = lg.id
    ;

My personal preference for such denormalized tables and views is:

  1. Avoiding spaces in column names

  2. Keeping it all lower case and using underscore to make any distinction instead of UsingDifferentCases

  3. Avoiding reserved (potentially reserved) keywords. For, e.g. I used lib_name instead of name above

  4. Create column names using shorter table/column aliases when/if appropriate to balance typing speed with legibility

Radagast
  • 5,102
  • 3
  • 12
  • 27
  • 1
    Please assume that `description` is used because of the existing naming convention. In this case, using `desc` instead of `description` would go against the (apparently existing) naming convention in the current code - maybe not as optimal as it could have been. But thank you for the ideas! – Timur Shtatland Nov 01 '21 at 17:06
-1

For an existing database, use the same naming convention used for other objects in the database when adding one new table or view.

If CustomerId is used in other tables and CustomerId is to be in the view, use CustomerId in the view if possible.

snj
  • 72
  • 8
  • **What if the names of the columns clash across the tables?** For example, two tables, each of them having columns named `id`,`name` and `description`. Specifically, see the original post for an actual example. This is the essence of the question. Could you please describe the naming convention in the example described in the original post? Thank you. – Timur Shtatland Nov 05 '21 at 16:37