Question

这里我如何做:

Table names are lower case, uses underscores to separate words, and are singular (e.g. foo, foo_bar, etc.
I generally (not always) have a auto increment PK. I use the following convention: tablename_id (e.g. foo_id, foo_bar_id, etc.).
When a table contains a column that is a foreign key, I just copy the column name of that key from whatever table it came from. For example, say table foo_bar has the FK foo_id (where foo_id is the PK of foo).
When defining FKs to enforce referential integrity, I use the following: tablename_fk_columnname (e.g. furthering example 3, it would be foo_bar_foo_id). Since this is a table name/column name combination, it is guaranteed to be unique within the database.
I order the columns like this: PKs, FKs, then the rest of columns alphabetically

是否有更好、更标准的方法这样做?

Answer 1

我要说的是,首先是前后一致的。

我认为,你们几乎在那里看到你在你提出的问题中概述的各项公约。两项评论:

第1和第2点是好的。

第3点——令人痛心的是,这并不总是可能的。关于您如何处理一个单列的表格:foo_bar,该表有以下几栏:foo_id和another_foo_id。这两条参考了<条码>foo>。您不妨考虑如何处理这一问题。这是一种独角兽案件,尽管!

Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.

第5点——我将避免这种情况。当你想在稍后日期增加或删除表格中的栏目时,它为你提供了很少的、将成为头痛。

其他要点如下:

<>《公约》/《议定书》>

您不妨提出一项指数命名公约,这将对您可能希望开展的任何数据库元数据工作大有帮助。例如,你可能只想用一个索引<代码>foo_bar_idx1或_foo_idx1——完全由你负责,但值得考虑。

<>Singular vs Plural Column name>

在您的栏目和表格名称中处理复数单的棘手问题也许是好的想法。这一主题往往导致big debate在亚洲开发银行社区。我将坚持表格名称和栏目的单体表格。在那里。我说过。

当然,这里的主要事情是一致性!

Answer 2

Consistency is the key to any naming standard. As long as it s logical and consistent, you re 99% there.

标准本身在很大程度上是个人的偏好,因此,如果你像你的标准一样,那么,标准本身就与标准一致。

回答你的问题——没有,我的SQL确实没有首选命名公约/标准,这样,你的自我提升是罚款(你看来是合乎逻辑的)。

Answer 3

我的SQL简短地描述了他们越来越严格的规则:

https://dev.mysql.com/doc/internals/en/coding-type.html。

西蒙·罗马教廷对我的SQL最常用的编码:

https://www.sqlets.guide/。

另见这一问题:

是否有任何已出版的电子格式准则?

Answer 4

幸运的是,正如我所知的一些发展社区一样,PHP开发商是“Camel案主”。

你们的公约是良好的。

简单地说,(b)一致——我看不到任何问题:

PS: Personally, I think 5) is overkill...

Answer 5

www.un.org/Depts/DGACM/index_spanish.htm 简单答复: 注

更有甚者,至少在Oracle或社区所鼓励的命名公约中,没有,但基本上你们必须了解识别特征的规则和限制,如MySQL文件所示:

关于你所遵循的命名公约,我认为,仅仅第5号是一点点不必要,我认为管理数据库的大多数视觉工具可以选择打上一栏名称(我使用DBeaver,而且有),这样,如果图谋对你的表格有清晰的直观介绍,你就可以使用我提到的选择。

By personal experience, I would recommed this:

Use lower case. This almost ensures interoperability when you migrate your databases from one server to another. Sometimes the lower_case_table_names is not correctly configured and your server start throwing errors just by simply unrecognizing your camelCase or PascalCase standard (case sensitivity problem).
Short names. Simple and clear. The most easy and fast is identify your table or columns, the better. Trust me, when you make a lot of different queries in a short amount of time is better having all simple to write (and read).
Avoid prefixes. Unless you are using the same database for tables of different applications, don t use prefixes. This only add more verbosity to your queries. There are situations when this could be useful, for example, when you want to indentify primary keys and foreign keys, that usually table names are used as prefix for id columns.
Use underscores for separating words. If you still want to use more than one word for naming a table, column, etc., so use underscores for separating_the_words, this helps for legibility (your eyes and your stressed brain are going to thank you).
Be consistent. Once you have your own standard, follow it. Don´t be the person that create the rules and is the first who breaking them, that is shameful.

而“Plural vs Singular”命名是什么? 这也是个人偏好的情况。在我的案件中,我试图使用表格的复数,因为我认为表格是内容的收集或包含内容的一揽子文件,因此,复数名称对我来说是有意义的;各栏的单数,因为我把一栏视为单独描述这些表格内容的属性。

Answer 6

Consistency is what everyone strongly suggest, the rest is upto you as long as it works.

For beginners its easy to get carried away and we name whatever we want at that time. This make sense at that point but a headache later.

foo foobar or foo_bar is great. We name our table straight forward as much as possible and only use underscore if they are two different words. studentregistration to student_registration

like @Zbyszek says, having a simple id is more than enough for the auto-increment. The simplier the better. Why do you need foo_id? We had the same problem early on, we named all our columns with the table prefix. like foo_id, foo_name, foo_age. We dropped the tablename now and kept only the col as short as possible.

Since we are using just an id for PK we will be using foo_bar_fk (tablename is unique, folowed by the unique PK, followed by the _fk) as foreign key. We don t add id to the col name because it is said that the name id is always the PK of the given table. So we have just the tablename and the _fk at the end.

为了限制,我们消除了所有因素,并加入了 came。 Case (tablename + Colname + Fk) foobarUsernameFk (forusername_fk col). 这只是我们所遵循的一种方式。我们保存着每个名字结构的文件。

When keeping the col name short, we should also keep an eye on the RESTRICTED names.

+------------------------------------+
|               foobar               |
+------------------------------------+
| id (PK for the current table)      |
| username_fk (PK of username table) |
| location (other column)            |
| tel (other column)                 |
+------------------------------------+

Answer 7

as @fabrizio-valencia said use lower case. in windows if you export mysql database (phpmyadmin) the tables name will converted to lower case and this lead to all sort of problems. see Are table names in MySQL case sensitive?

友情链接