December 8, 2017 at 7:10 am
john.denniston - Friday, December 8, 2017 1:46 AMI would say:
* It doesn't matter very much.
* For me a table is a collection of X; so I use plural, just as I would for a list or an array in any other programming language. Similarly if I have a stored procedure designed to get a single value/row I use singular whereas if it gets multiple rows I use a plural.
This perspective makes the most sense to me. Plural was my natural inclination when I started out doing my own stuff, and I still like it. Here at work, singular tends to get used, so I go along with that for consistency. I think consistency is more important.
December 8, 2017 at 7:14 am
UltimatelyI'm not sure that it matters much in any particular application
I think one word sums up the entire debate. The only thing that matters is:
Consistency
I have my preference that I use, but I am the only developer at my company. When I am working on a team of developers I need to follow the standard set forth at that company. The goal everyone should have is reduced defects and readability goes a long way towards that goal.
Dave
December 8, 2017 at 7:25 am
I prefer singular because i like to have the id named the same as the table. So I have a table "Person" the id is "idPerson". The id is representing a single person so the table must be singular to keep the same name between the table and the id.
December 8, 2017 at 7:31 am
I would push back on anything that prefixes an object to tell you what type of object it is such as tbl, vw, fn, usp etc
I would push back on your push back. For starters, prefixes are an excellent way to avoid reserved words. I hate worked with tables where I have to remember to put the brackets around Date when dtmCompleted or dtCompleted will do just fine.
December 8, 2017 at 7:40 am
Hey, Steve, you got on my case about the political thing, and now here we go on the singular/plural issue. 🙂 Not very PC this morning.
The comment regarding less typing takes me back to our early days when we had machines with 16k and, if we were lucky, 32k of memory to work with and run whole business enterprises. I recall a conversation when a co-worker tried to make the case for shorter names taking less storage! he liked data names such as AA, AB, AC, etc. OK, I guess you could in fact get a longer statement on a single punch-card. Internally weren't they all the same?
And then there were the days when one of the items in developer productivity considered lines of code produced per hour/day/week. Longer names lead to more lines. I could never relate to that one. Those oversized program card decks really were a pain to handle.
But there are some small blessings in the realm of names. I've always been thankful that my parents shortened Richard to Rick instead of Dick!
And while I'm on here, I wish you all a Merry Christmas, not Merry Xmas!
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
December 8, 2017 at 7:51 am
I prefer singular, but where I work it was plurals that won out. However, we ended up with Patients rather than Patient, but PatientAddresses not PatientsAddresses.
Ultimately, I also vote for consistency as I find that makes things easier overall.
December 8, 2017 at 8:03 am
RonKyle - Friday, December 8, 2017 7:31 AMI would push back on anything that prefixes an object to tell you what type of object it is such as tbl, vw, fn, usp etc
I would push back on your push back. For starters, prefixes are an excellent way to avoid reserved words. I hate worked with tables where I have to remember to put the brackets around Date when dtmCompleted or dtCompleted will do just fine.
Would you also use intRecordId, decSalesValue etc? For consistency, you would have to, if you go down that route.
Using datatype prefixes on column names is a bad idea, in my opinion. CompletedOn or CompletedAt would be my versions.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 8, 2017 at 8:05 am
Also at the expense of maybe seeming to be an old-timer and set in my ways, and along the lines of keying efficiency, can somebody please explain to me why we had to take the step of adding square brackets for qualified identifiers? This has always seemed to me to be a rather silly thing to be added. Does it provide anything for design and coding that we couldn't already accomplish?
I see Phil has also posted the preceding that addresses this same thing while I was thinking about it. Great minds?
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
December 8, 2017 at 8:19 am
Now we're getting to the question of whether it is an 'item' table or a table of 'items', a 'customer' table or a table of 'customers'.. HO HUM. Either way, I don't need the square brackets...
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
December 8, 2017 at 8:19 am
My experience is similar to what you describe. I started out using the plural, and I think mostly that's what I inherited, or learned. As I began to design my own schemas from the ground up, I began to think of a table as a class being modeled, with each record being an instance of the class. For me it also makes the SQL syntax read more like English. When doing joins, etc. it seems more natural to think of 'ThisObject.ThatAttribute" I often find myself thinking of views in the plural however, since they tend to be the culmination of the set of objects.
December 8, 2017 at 8:22 am
I'd rather focus on normalization of the data model as a whole rather fretting over naming conventions, because there are functional consequences for getting the normalization wrong. But, yes, consistency within the database is certainly important, and I can live with oddly named tables so long as it's descriptive and the columns are named consistently across tables. I can't imagine anyone looking at a table called Customer and assuming it contains rows for a single customer. So I typically use the singular form for table tables (ex: Customer), because the table name can then be used as a prefix for other objects without the need to alternate between singular / plural form, and the singular form just sounds better when prefixing columns or stored procedures.
For example:
table [Customer]
column [CustomerID]
column [CustomerBirthDate]
stored procedure [usp_UpdateCustomer]
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 8, 2017 at 8:25 am
KenpoDBA - Friday, December 8, 2017 4:02 AMWhen I first started, it was singular all the way. Then I discovered the problem with it. Reserved words. You have to work to not use reserved words. Look at an Order table. That's a reserved word so you have to use something like CustOrder or cOrder, etc. That's just extra useless typing... just use Orders and it's not only intuitive, but it keeps you just having to contrive a prefix that either doesn't make sense or is redundant. I mean, who else would place an order, of course it's a customer!
I also don't think that consistency is necessary. I think it's more important to name objects in the way people think of them. However the main users view the data is going to be the easiest way for them to query it and it'll make sense to them. So do that. If that means that some tables are plural and some are singular then ok. Do that.I studied formal modeling so I get the singular argument, but don't forget it's all made up anyway. From a modelers perspective, it helps to think of everything as a single entity, but once it becomes an actual DB, nobody else thinks like that. So why force modeling rules onto your users? And if a modeler looks at an Orders table and thinks that each row holds multiple orders then he's just dumb and doesn't deserve to be modeling your DB. This is all made up people. And there are few actual modelers anymore. I used the singular naming scheme for years but I just got tired of coming up with unnatural names to get around reserved words.
Besides, if you want to talk about consistency, just take a look at some of my tables for my Minion Maintenance products. I have a Log table and a LogDetails table. The log table holds an overview log of how the overall job went. The LogDetails table holds details for individual records. It doesn't hold just 1 detail, it holds dozens. But the Log table only holds a single log for each run. And while we can get bogged down in a discussion that the LogDetails table holds only one detail row per object, that's unnatural. When you talk about it you say, can I see the details for this specific index rebuild... you don't say can I see the detail?
So since the rules are all made up, and it's clearly not a performance thing, do whatever you like, and give your tables nice, natural names. Don't worry about consistency. Worry about being natural and make a nice DB.
When considering an Order table, what kind of Order table? I have no clue what a CustomerOrder is but I do know what a SalesOrder or PurchaseOrder is related to in a system. I have found that I initially started using plural for names of tables but quickly shifted to the singular.
December 8, 2017 at 8:27 am
Eric M Russell - Friday, December 8, 2017 8:22 AMI'd rather focus on normalization than naming conventions, but yes, consistency within the database is important. I can't imagine anyone looking at a table called Customer and assuming it contains rows for a single customer, so I typically use the singular form for table tables (ex: Customer), because the table name can then be used as a prefix for columns and stored procedures within needing to change it's form.
For example:
table [Customer]
column [CustomerID]
column [CustomerBirthDate]
stored procedure [usp_UpdateCustomer]
Somewhat tangentially, this raises another point. And this time, I think I am in the minority 🙂
Why do people use a suffix of ID rather than Id? Id abbreviates 'Identifier'. ID abbreviates 'Identity Document'.
Unless you are storing a customer's passport or driving licence information, CustomerId is the more accurate form.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 8, 2017 at 8:31 am
Phil Parkin - Friday, December 8, 2017 8:27 AMEric M Russell - Friday, December 8, 2017 8:22 AMI'd rather focus on normalization than naming conventions, but yes, consistency within the database is important. I can't imagine anyone looking at a table called Customer and assuming it contains rows for a single customer, so I typically use the singular form for table tables (ex: Customer), because the table name can then be used as a prefix for columns and stored procedures within needing to change it's form.
For example:
table [Customer]
column [CustomerID]
column [CustomerBirthDate]
stored procedure [usp_UpdateCustomer]Somewhat tangentially, this raises another point. And this time, I think I am in the minority 🙂
Why do people use a suffix of ID rather than Id? Id abbreviates 'Identifier'. ID abbreviates 'Identity Document'.
Unless you are storing a customer's passport or driving licence information, CustomerId is the more accurate form.
If I were your manager, and you raised that question, I'd find you more work to do. OK, just kidding. ID vs. Id doesn't matter to me unless we've agreed upon CamelCase convention. 😀
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 8, 2017 at 8:32 am
RonKyle - Friday, December 8, 2017 7:31 AMI would push back on anything that prefixes an object to tell you what type of object it is such as tbl, vw, fn, usp etc
I would push back on your push back. For starters, prefixes are an excellent way to avoid reserved words. I hate worked with tables where I have to remember to put the brackets around Date when dtmCompleted or dtCompleted will do just fine.
My question would be what kind of Date. Is it a SalesDate (or DateOfSale), DateUpdated, or as you mention, DateCompleted? Using just Date doesn't tell you anything about the value being stored in that column any more than a column named Id.
I would stay away from an abbreviation in adding a prefix or suffix to a column name unless such an abbreviation or acronym actually made sense. So DateCompleted instead of dtmCompleted.
Viewing 15 posts - 16 through 30 (of 81 total)
You must be logged in to reply to this topic. Login to reply