Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a foreign key that references the users table id column would be: creatorUserId Now the thing is that right now I am building my application and for the sake of development speed, I am going to be referencing everything by a surrogate foreign key (INT AUTO_INCREMENT). I really don't want to get into the whole which is faster than what in which case this early in development, I rather leave that stuff to the end (to try to prevent premature optimizations and I am more interested in working on the front-end of this application then the backend). With that being said, a concern I have is if I in-fact want to change some of the foreign keys from the surrogate key to a natural key (all relavent tables will have both) and I use column names like creatorUserId I am going to have to go through all the backend code in order to make the change, a process that would not be very pleasant. I was thinking about doing something that just use the function and table name for foreign keys column like: creatorUser In the case, if I change what column I am using for the foreign key, the backend code changes are a lot simpler and there would be a lot less of them (if you are using an ORM which I am). Also, since surrogate keys are generally numbers and natural keys are generally alpha characters, it should still be relative easy to know which one is being used just by looking at the value. Would this be considered bad practice?

I would like some expert opinion on best practices when it comes to **column naming**. The background is that [according to Wikipedia](http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join) , the following syntax, SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID); is more efficient than SELECT ... FROM Employees JOIN Timesheets ON (Employees.EmployeeID = Timesheets.EmployeeID); However, the JOIN ... USING syntax only works of *all primary key columns have globally unique names*. Thus I wonder if this is considered The Right Thing to do. Personally, I always used to create tables with PK column id, and foreign key column othertable_id. But that way it's not possible to use USING or NATURAL JOIN. Any links to design styles or best practice guides for table design would be appreciated, too!

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 INNER JOIN otherTable t2 ON t1.id=t2.id ; But... Why is this acceptable in stored procedures and such? It seems like all it does is harm the readability of the statement while saving an extremely minor amount of time. Is there any functional or logical reason to do this? It seems to add ambiguity rather than remove it; the only acceptable reason I can see for using this format is if you were adding a semantically meaningful alias -- for example, FROM someTable idsTable -- when the table name isn't descriptive enough. Is table aliasing a bad practice or is this just a misuse of a helpful system?

Below I attached diagram which corresponds to part of my database : My questions: 1. In your opininon Do I have to divide ServiceInsance and ConfServiceInstance ? I did it like that because I would like to have separated IDs and Configuration columns. 2. What do you think about name of these tables ? I mean Is it worth to have Dict and Conf in table names ? 3. Is it a problem that PK does not corespond to table name by convention: TableName + ID in Conf and Dict tables ? 4. Is it a better idea to create schemas conf and dict maybe ?

I see people talking about the best way to name foreign key constraints. I have never manually named them, I just get whatever my RDBMS gives it, like tableName_ibfk_1. If I've never seen a need to name them, should I care about this? Why do some people care?

Given this setup in current Postgres 9.4 (from this related question ): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') , (5, 'D') , (9, 'E'); *dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=1cbb193db0f3662c4192ec82b0f5e609)* (also from the previous question). I wrote a SELECT with a FULL JOIN to achieve the objective of the referenced question. Simplified:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

As per specifications, the correct way to address the column ts is without table qualification. Either of the *input* values (f.ts or b.ts) can be NULL. The USING clause creates a bit of an odd case: introducing an "input" column that's not actually present in the input. So far so elegant. I put this in a plpgsql function. For convenience (or requirements) I want the same column names for the result of the table function. So we have to avoid naming conflicts between identical column names and function parameters. Should best be avoided by picking different names, but here we are:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Bold emphasis to highlight the **problem**. I can't use ts without table qualification like before, because plpgsql would raise an exception (not strictly necessary, but probably useful in most cases): > ERROR: column reference "ts" is ambiguous > LINE 1: SELECT ts, f.foo, b.bar > ^ > DETAIL: It could refer to either a PL/pgSQL variable or a table column. I know I can use different names or a subquery or use another function. But I wonder if there's a way to reference the column. I can't use table-qualification. One would think there ***should*** be a way. Is there?

**How should I name my Tables when creating a new database?** Singular: Client or Plural: Clients?

We're using a database setup from a vendor's application that has horrifically hard to read database table names, and no documentation on what is stored where. I can see why one might want to obfuscate their table structure in a proprietary app, but one of the selling points of this application (Enterprise Resource Planning) was it's customizability. Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise. To the best of my knowledge the length of the table name won't affect performance noticeably, correct? The database is very complex (hundreds of tables) so sorting makes sense, but I can't imagine why AccountsPayableTransactions isn't preferable to aptrx....

I know that PostgreSQL has 8 level of table locks which are, from least restrictive to most: 1. ACCESS SHARE 2. ROW SHARE 3. ROW EXCLUSIVE 4. SHARE UPDATE EXCLUSIVE 5. SHARE 6. SHARE ROW EXCLUSIVE 7. EXCLUSIVE 8. ACCESS EXCLUSIVE However, I can't really determine any pattern or convention to these names. There are literally 5 words total: ACCESS, SHARE, ROW, EXCLUSIVE, and UPDATE, which are grouped together, seemingly at random, for all 8 levels. I find myself preferring to refer to them as just L1 through L8 rather than their actual names. Are these names just the result of historical conventions that evolved organically in a way which doesn't really have any meaning besides "look at the docs and see what level each is?"

When I am looking to create some timestamp fields (or other date/time style fields), what is the best way to name them? Should I just put record_timestamp?

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase. In my PostgreSQL database, I currently have all my columns named following a camelCase convention, but I am thinking: is that the best idea? Should I use snake_case in the database columns and convert them in the back-end?

I have a SQL Database on Azure that is giving me a performance recommendation for an index. What does the "wi" part of the name? I'm guessing something to do with the include part of the statement. With Include?

CREATE NONCLUSTERED INDEX [nci_wi_TableName_468BA2FCEC4455FB9A6ACA94430E4935] ON [dbo].[TableName] ([Id]) INCLUDE ([ColA], [ColB]) WITH (ONLINE = ON)

I'm working on a database design that extensively uses UUIDs for PRIMARY KEYs. However, this confronts me with a very consequential choice. How do I name these columns? I would call them uuid, except that, UUID being an identifier, I then have to quote the field name everywhere:

CREATE TABLE thingie (
  "uuid" UUID PRIMARY KEY DEFAULT public.gen_random.uuid(),
  foo VARCHAR,
  bar VARCHAR,
);

A straight-forward alternative seems to be to instead call these columns id:

CREATE TABLE thingie (
  id UUID PRIMARY KEY DEFAULT public.gen_random.uuid(),
  foo VARCHAR,
  bar VARCHAR,
);

That way, I don't have the column name, and semantically I can argue that a UUID is indeed a sort of ID; in a Venn diagram, the UUID circle would be placed wholly in the ID circle. *However*, I (and I am sure many others with) have grown so accustomed to associating id with an auto-incrementing INTEGER column that I'm afraid to break some sort of unspoken rule by calling these IDs ids. If you would brighten up my confusion with some solid bike-shedding, I would be much obliged. Indeed my question is: **How would _you_ call your UUID-typed surrogate keys and why?**

I’m watching some Brent Ozar videos (like this one, for instance ) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”. **Questions and considerations** - **It this really a problem?** Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization). - **Is this something that I need to get rid of?** I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.

My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view. I was thinking of naming the view either of these 3 ways: gizmo_quality_metrics gizmo_quality_metrics_v v_gizmo_quality_metrics Which one should I choose? This may need to be renamed (or not) a few years later, when switching it to a materialized view: gizmo_quality_metrics gizmo_quality_metrics_mv mv_gizmo_quality_metrics From the way I worded the question, you get the idea that I favor keeping the same name (gizmo_quality_metrics), which is based on the data, and disregard the implementation (thus, no prefixes and extensions). However, I have seen all of the above naming conventions in practice, and I am wondering what is the most maintainable one for the long term. --- 1 I am simplifying - of course, they are not gizmos. They are actually deoxyribonucleic acid (DNA) molecules, if you really want to know. But this is irrelevant. --- **[EDIT, Mar 18, 2022]** Related question (thanks to *a_horse_with_no_name* for mentioning it!): https://dba.stackexchange.com/q/154251/192715 Note that I do not link to any one particular answer in that literal *wall* of answers, from high to low rep users. It is instructive to read a few, though.

I know this might be a silly question. But I really appreciate accepting and answering this question. When I have an OLTP database with some big tables like orders and items. I have other tables (similar to dimension tables in OLAP) where we store reference values, such as: currency, city, category. What is the proper name for those reference tables? Can we still call them dimension tables in OLTP or do they have their own generic name?

Maybe double post but I could not find any answer here. I saw couple of posts how to name my tables and I understood that (semi-officially) it should contain lower cases, should be underscored and singular (f.e. my_table) My question arose when I had to do the following. I have an app where I have user, platform and user_info, where platform is what the user is working on (f.e. MAC, PC, LINUX). If I need to create junction table between user and platform, how do I name it in order the next person browsing the schema to understand that user and platform have a junction table named user_platform, but to not be confused when he sees the table user_info? BR

Seems the Log Shipping backup files naming convention (especially the date-time stamp in the name) is followed by UTC time, i believe that is default and standard. I would like to clarify on following cases: 1. I'm wondering if there any possibility to change the naming convention to follow the date-time as per local time-zone from the server where the BACKUP performed. 2. Is the naming convention (UTC time stamp as suffix) really matter, since anyway the Log Shipping copy/restore job considers the LSN between each backup file to maintain valid log backup chain. The aim is to avoid issues during compressing process that i'm following to maintain one zip file per day, and as per following screenshot it would combine additional files (20191015xxxxx) which are belongs different date (2019-10-16)

In this draft of SQL:1992 , I found… Section 5.2 > Leveling Rules > > .... > > 2. The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: > a) No ` or **shall contain more than 18 `s**. …and… > Annex A.1 > 1. Subclause 5.2, "` and `": > a) In conforming Intermediate SQL language, a ` or a **shall not comprise more than 18 `s.** > b) A ` shall not be a or a `. > c) Conforming Intermediate SQL language shall contain no ` that ends in an `. ## For *implementors* of SQL, or for *users* of SQL? I searched the document multiple times, but could never get a sense of the meaning of identifier. - Is the spec referring to labeling within an *implementation* of SQL (such as PostreSQL or Oracle)? So labels used within the SQL product cannot contain identifiers that are over 18 characters or end in an underscore. - Or is the spec referring to *users* of a SQL implementation? So we who are naming our schemas, tables, columns, and other such database objects cannot use names over 18 characters or end in an underscore?

I am currently adding a feature in our software where we save a timeout in the database to initialize cronjobs which get active after that timeout. To model this I see different ways but can't say which of them is the cleanest: 1. Create 2 columns "timeout" and "timeout_measure", where *timeout_measure* is a MySQL Enum with seconds, minutes, hours, etc. 2. Create 1 column "timeout" where the timeout is always stored in seconds Even though I tended to the 1. way at first I saw the problem that additionally to this second column a MySQL trigger would be needed to check that *if a timeout is set, the timeout_measure cannot be NULL* , which would be an unnecessary performance hit as far as I am concerned, because we can implement the same functionality with only one column where we save the seconds of the timeout. So I am now tending to the 2. way but I don't like the readability of this. Having a column like so with entries in seconds, one might not understand but only guess what this means without reading a documentation of this table. | timeout | | 3600 | | 25200 | | 86400 | So I thought about saving the unit of measurement in the column name like *timeout_in_secs*. But then I am curious if this wouldn't be kind of boilerplate because if there is a documentation this additional info would not be needed in each query made and whenever the column is referenced at in the code. So whenever one wants to reference to the column of this entity in the sourcecode (we use NodeJS and Sequelize as ORM) he would need to write obj.timeoutInSecs instead of just obj.timeout. What to do? I don't want to change the whole table structure once this feature is productive, since this could end up in a big mess. Do you think it suffices to have this documented in the database documentation, or would you still go to choose *timeout_in_secs* as the column name? EDIT: I know this might be opinion based but in this case different opinions and experiences might help a lot. I am on my own in this decision and simply can't decide which solution is the best/cleanest.