sql - style - database table naming




表命名困境:單數名稱與復數名稱 (20)

學術界認為,表格名稱應該是它們存儲屬性的實體的單數。

我不喜歡任何需要圍繞名稱的方括號的T-SQL,但我已將Users表重新命名為單數,永遠判斷使用表的人有時必須使用括號。

我的直覺是,用單數來保持是更正確的,但我的直覺是括號表示不喜歡的東西,例如列名中有空格等等。

我應該走還是留?


Joe Celko在其著作“ SQL編程風格 ”一書中建議,集合(例如表格)應以復數形式命名,而標量數據元素(例如列)應以單數形式命名。

他引用ISO-11179-4作為元數據命名的標準,該標準支持這一指導原則。

讓我爭論為什麼這是有道理的。

  1. 表是一組。 表中的每一行都是一個對象(columns = fields)。 在編程中,您使用複數名稱命名集合( 數組和集合應該有復數名稱來表示它們是對象的集合而不是單個對象 ),例如Java程序中的args
  2. Java參數是複數形式,因為它們可以容納0,1和數百萬個參數。
  3. for every student in students迭代它們,而不是for every student in students for every s in student 。 您從一組學生中選擇一部分學生。 您不會從學生中選擇一部分學生。 這是正確命名的概念性原因。

揭穿謬誤。 最流行的答案說

原因1 (概念)。 你可以想像包含蘋果的袋子,比如“AppleBag”,如果包含0,1或者一百萬個蘋果,它並不重要,它總是一樣的包。 表格就是這樣的容器,表格名稱必須描述它包含的內容,而不是包含多少數據。 此外,複數概念更多地是關於口語(實際上是為了確定是否存在一個或多個),表格不打算由人讀取。

讓我們轉換AppleBag => BagOfApples。 現在,同樣的“概念性”論證與其本身相反,我們看到蘋果公司,因此答案必須是複數

這是因為這個****沒有任何概念。 它甚至不能推斷英語,簡單的邏輯。 用英語,一個BagOfApples != AnApple

“Bag包含0,1,...數百萬個”蘋果的論點只是證明集合必須被稱為“蘋果”,同樣也是Java參數或.com/posts 。 不知何故,文明的敵人設法得出結論,單數必須在這裡使用。 帖子只是一個文件夾。 為什麼它應該是複數?

讓我們教先生吧。 Artuwood的一些邏輯: folder is a folder and must describe what it contains, not how much data it contains

事實上,如果你開始認為你意識到apple contains apple無稽之談。 真正的概念是Bag contains Apples 。 要么我們命名我們的包裝袋(特定種類),或者想一想它包含的是什麼, 蘋果 (那些骯髒的混蛋試圖減少這個數字問題,但我們是在蘋果之後,而不是它們的數量)。 是的,如果我們將包裝器抽像出來,我們意識到我們在Apples之後,它包含了什麼。 我們獲取並迭代Bag或Apples,而不是Apple。

原因2 。 (方便)。 用單數名稱比用複數名稱更容易。 對象可以有不規則的複數或不復數,但總會有單數(除了少數例外,如新聞)。

客戶訂單用戶狀態新聞

他談論哪種便利? 讓我們指出,複數比單數更簡單。

原因3 。 (美學和秩序)。

Aestetics就在我們這邊。 和他一樣,這很簡單。 我們只是聲稱這一點,這就是轉換錶所需的全部內容。

原因4 (簡單)。 放在一起,表名,主鍵,關係,實體類...更好地意識到只有一個名稱(單數)而不是兩個(單數類,複數表,單數域,單數 - 複數主 - 細節.. 。)

我是否僅僅為了簡單而看到,一切都必須變得單一? 是的,忘記使用英語的複數。 它會讓事情變得更簡單。

事實上,在許多語言中,性關係到所有的事物,而不僅僅是男孩和女孩。 我注意到它簡化了參考。 當我用俄語說“狐狸,狗和狼”,然後說“他”時,它毫不含糊地表示我是指“狼”,因為“狐狸”和“狗”是“她”。 現在你認為有助於減少歧義的區別創造了它。 怎麼會這樣?

可能的邏輯是“讓我們用我們的語言來保持任意構造,並通過刪除有意義的規則來強化這種混亂”。 是的,在邏輯要求復數時使用單數的建議,同時保持將不適當的屬性(如性別)附加到不適當的項目上是在我們瘋狂的世界中追求廢話。

作為

SELECT activity.name讀取比SELECT activities.name更好

您可能需要SELECT student.name FROM students as student

好的,這裡可能是參數:如果table是複數,table的別名如何是單數? 好的,這是有道理的。 但是說列(對象的屬性)是單數的,因此,對象集合也必須是單數的,是無稽之談。

原因5 。 (全球化)。 世界越來越小,你可能有一個不同國籍的團隊,並不是每個人都有英語作為母語。 對於非本地英語程序員來說,更容易想到“存儲庫”而不是“存儲庫”,或者避免他們鍵入“狀態”而不是“狀態”。 使用單數名稱可以減少由錯別字造成的錯誤,避免花費額外的時間思考“是孩子還是孩子?”,從而節省時間,從而提高生產力。

為什麼像我這樣的其他非母語人士為了簡單化和國際化而購買廢話? 為什麼不改進我們的英語並保持邏輯一致? 畢竟,在這種情況下理解語言要容易得多。

否則,為了簡單起見,我們也應該放棄動詞,文章,形容詞,只用名詞說話。 如果我們將言語限制在moooo而不是其他任何東西,那麼動物能夠說英語也會更簡單(也更有效)。 這樣我們實現了更廣泛的國際化。

原因6 。 (為什麼不?)。 它甚至可以節省您的寫入時間,節省磁盤空間,甚至讓您的電腦鍵盤持續更多!

這個觀點支持我們為什麼在人類交流中也應該放棄複數。 不,不是moooo, mo moo, moo moo ,我們會說I, III, III, II 。 比單獨提案短得多。

單數唯一有意義的論據是表格是一組非常特殊的對象。 表是一個類 ! 是的,它包含特定類型的所有對象,我們使用單數來表示類名。 類Dog包含所有的狗。 什麼是dog1? 它是一隻狗。 另一方面,用戶將表格作為集合來處理。 他們添加/刪除項目收集,我們使用複數收集。


As others have mentioned here, conventions should be a tool for adding to the ease of use and readability. Not as a shackle or a club to torture developers.

That said, my personal preference is to use singular names for both tables and columns. This probably comes from my programming background. Class names are generally singular unless they are some sort of collection. In my mind I am storing or reading individual records in the table in question, so singular makes sense to me.

This practice also allows me to reserve plural table names for those that store many-to-many relationships between my objects.

I try to avoid reserved words in my table and column names, as well. In the case in question here it makes more sense to go counter to the singular convention for Users to avoid the need to encapsulate a table that uses the reserved word of User.

I like using prefixes in a limited manner (tbl for table names, sp_ for proc names, etc), though many believe this adds clutter. I also prefer CamelBack names to underscores because I always end up hitting the + instead of _ when typing the name. Many others disagree.

Here is another good link for naming convention guidelines: http://www.xaprb.com/blog/2008/10/26/the-power-of-a-good-sql-naming-convention/

Remember that the most important factor in your convention is that it make sense to the people interacting with the database in question. There is no "One Ring to Rule Them All" when it comes to naming conventions.


I am a fan of singular table names as they make my ER diagrams using CASE syntax easier to read, but by reading these responses I'm getting the feeling it never caught on very well? I personally love it. There is a good overview with examples of how readable your models can be when you use singular table names, add action verbs to your relationships and form good sentences for every relationships. It's all a bit of overkill for a 20 table database but if you have a DB with hundreds of tables and a complex design how will your developers ever understand it without a good readable diagram?

http://www.aisintl.com/case/method.html

As for prefixing tables and views I absolutely hate that practice. Give a person no information at all before giving them possibly bad information. Anyone browsing a db for objects can quite easily tell a table from a view, but if I have a table named tblUsers that for some reason I decide to restructure in the future into two tables, with a view unifying them to keep from breaking old code I now have a view named tblUsers. At this point I am left with two unappealing options, leave a view named with a tbl prefix which may confuse some developers, or force another layer, either middle tier or application to be rewritten to reference my new structure or name viewUsers. That negates a large part of the value of views IMHO.


I don't like plural table names because some nouns in English are not countable (water, soup, cash) or the meaning changes when you make it countable (chicken vs a chicken; meat vs bird). I also dislike using abbreviations for table name or column name because doing so adds extra slope to the already steep learning curve.

Ironically, I might make User an exception and call it Users because of USER (Transac-SQL) , because I too don't like using brackets around tables if I don't have to.

I also like to name all the ID columns as Id , not ChickenId or ChickensId (what do plural guys do about this?).

All this is because I don't have proper respect for the database systems, I am just reapplying one-trick-pony knowledge from OO naming conventions like Java's out of habit and laziness. I wish there were better IDE support for complicated SQL.


I only use nouns for my table names that are spelled the same, whether singular or plural:

moose fish deer aircraft you pants shorts eyeglasses scissors species offspring


I personaly prefer to use plural names to represent a set, it just "sounds" better to my relational mind.

At this exact moment i am using singular names to define a data model for my company, because most of the people at work feel more confortable with it. Sometimes you just have to make life easier to everyone instead of imposing your personal preferences. (that's how i ended up in this thread, to get a confirmation on what should be the "best practice" for naming tables)

After reading all the arguing in this thread, i reached one conclusion:

I like my pancakes with honey, no matter what everybody's favorite flavour is. But if i am cooking for other people, i will try to serve them something they like.


I've actually always thought it was popular convention to use plural table names. Up until this point I've always used plural.

I can understand the argument for singular table names, but to me plural makes more sense. A table name usually describes what the table contains. In a normalized database, each table contains specific sets of data. Each row is an entity and the table contains many entities. Thus the plural form for the table name.

A table of cars would have the name cars and each row is a car. I'll admit that specifying the table along with the field in a table.field manner is the best practice and that having singular table names is more readable. However in the following two examples, the former makes more sense:

SELECT * FROM cars WHERE color='blue'
SELECT * FROM car WHERE color='blue'

Honestly, I will be rethinking my position on the matter, and I would rely on the actual conventions used by the organization I'm developing for. However, I think for my personal conventions, I'll stick with plural table names. To me it makes more sense.


I've always used singular simply because that's what I was taught. However, while creating a new schema recently, for the first time in a long time, I actively decided to maintain this convention simply because... it's shorter. Adding an 's' to the end of every table name seems as useless to me as adding 'tbl_' in front of every one.


If we look at MS SQL Server's system tables, their names as assigned by Microsoft are in plural .

The Oracle's system tables are named in singular . Although a few of them are plural. Oracle recommends plural for user-defined table names. That doesn't make much sense that they recommend one thing and follow another. That the architects at these two software giants have named their tables using different conventions, doesn't make much sense either... After all, what are these guys ... PhD's?

I do remember in academia, the recommendation was singular.

For example, when we say:

select OrderHeader.ID FROM OrderHeader WHERE OrderHeader.Reference = 'ABC123'

maybe b/c each ID is selected from a particular single row ...?


My take is in semantics depending on how you define your container. For example, A "bag of apples" or simply "apples" or an "apple bag" or "apple".

Example: a "college" table can contain 0 or more colleges a table of "colleges" can contain 0 or more collegues

a "student" table can contain 0 or more students 
a table of "students" can contain 0 or more students.

My conclusion is that either is fine but you have to define how you (or people interacting with it) are going to approach when referring to the tables; "ax table" or a "table of xs"


Tables: plural

Multiple users are listed in the users table.

Models: singular

A singular user can be selected from the users table.

Controllers: plural

http://myapp.com/users would list multiple users.

That's my take on it anyway.


The system tables/views of the server itself ( SYSCAT.TABLES , dbo.sysindexes , ALL_TABLES , information_schema.columns , etc.) are almost always plural. I guess for the sake of consistency I'd follow their lead.


We run similar standards, when scripting we demand [ ] around names, and where appropriate schema qualifiers - primarily it hedges your bets against future name grabs by the SQL syntax.

SELECT [Name] FROM [dbo].[Customer] WHERE [Location] = 'WA'

This has saved our souls in the past - some of our database systems have run 10+ years from SQL 6.0 through SQL 2005 - way past their intended lifespans.



單數。 我不買任何最符合邏輯的論點 - 每個人都認為他自己的偏好是最合乎邏輯的。 不管你做什麼,它都是一團糟,只是選擇一個慣例並堅持下去。 我們試圖用具有非常特定語義的高度規則(SQL)語法將具有高度不規則語法和語義(正常語言和書面語言)的語言映射為語言。

我的主要觀點是,我不認為這些表格是集合,而是關係。

所以, AppUser關係告訴哪些實體是AppUsers

AppUserGroup關係告訴我哪些實體是AppUserGroups

AppUser_AppUserGroup關係告訴我AppUsersAppUserGroups是如何關聯的。

AppUserGroup_AppUserGroup關係告訴我AppUserGroupsAppUserGroups是如何相關的(即組的成員)。

換句話說,當我思考實體及其相關性時,我想到的是單數關係,當然,當我想到集合或集合中的實體時,集合或集合就是複數。

在我的代碼中,然後在數據庫模式中,我使用單數。 在文字描述中,我最終使用複數來提高可讀性 - 然後使用字體等來區分錶/關係名和復數。

我喜歡把它看作是混亂的,但是系統的 - 這樣一來,我總是會為我想表達的關係系統地產生一個名稱,這對我來說非常重要。


單數。 我會調用一個包含一堆用戶行表示對象'用戶'的數組,但表是'用戶表'。 認為表格只是它所包含的一組行是錯誤的,IMO; 該表是元數據,並且這組行是分層次地附加到表上的,它不是表本身。

當然,我始終使用ORM,並且使用複數表名寫入的ORM代碼看起來很蠢。


就“標準”而言,其他人已經給出了相當好的答案,但我只是想補充一點......“用戶”(或“用戶”)實際上可能不是表中數據的完整描述? 並不是說你應該對錶名和特殊性過分瘋狂,但可能像“Widget_Users”(其中“Widget”是你的應用程序或網站的名稱)更合適。


我也會用複數形式 ,並與上述用戶的困境,我們採取方括號方法。

我們這樣做是為了在數據庫體系結構和應用程序體系結構之間提供統一性,並且深刻理解Users表是用戶值的集合,就像代碼工件中的用戶集合是用戶對象的集合一樣。

讓我們的數據團隊和我們的開發人員講同樣的概念語言(儘管不總是相同的對象名稱)可以更容易地在他們之間傳達想法。


我堅持表格名稱和任何編程實體。

原因? 事實上,英語中存在不規則的複數,如小鼠⇒老鼠羊⇒羊 。 那麼,如果我需要一個集合 ,我只是使用鼠標 ,然後繼續前進。

它確實可以幫助眾多人脫穎而出,並且我可以輕鬆地並以編程方式確定收集的東西的樣子。

所以,我的規則是:一切都是單數,每一個東西的集合都是單數的,附加一個。 也幫助使用ORM。


我更喜歡使用未經反應的名詞,這在英語中恰好是單數。

忽略表名的數量會導致正字法問題(與許多其他答案一樣),但選擇這樣做是因為表通常包含多行,而且在語義上也充滿了漏洞。 如果我們考慮一種基於大小寫來反應名詞的語言,那麼這一點就更為明顯(正如大多數人所做的那樣):

既然我們通常會對行進行一些操作,為什麼不把這個名字放在這個指稱的情況下呢? 如果我們寫的表格比我們讀的表格要多,為什麼不把名字放在表達式中? 這是一個表格,為什麼不使用屬格? 我們不會這樣做,因為表格被定義為一個抽象容器,無論其狀態或用法如何,它都存在。 如果沒有精確和絕對的語義原因而忽略名詞就是喋喋不休。

使用未被反射的名詞是簡單的,邏輯的,規則的和語言無關的。





naming-conventions