2

Im getting confused on this day fixing the database for having the consistent field name.

When i want to name the field to explain: the name of the one who locked, or hidden the post. What's the field should i use?

locked_by_username , lock_by_username or lock_username hidden_by_username , hide_by_username or hidden_username ?

Present tense or past tense? and should i use by inside the field name, does it a bad practice?

I see many database use post_username or poster_username to describe the username of the one who posted the post.

But with lock and hide verb, i can't see any the good way for describing it.

Sorry, i know this is a really stupid question, but English isn't my native language.

TomSawyer
  • 3,711
  • 6
  • 44
  • 79
  • You could use `locking` or `hiding`, but I've never seen a problem with past tense either as long as the field name is not ambiguous (_such as a `void_trans` in a trans table that could point to the trans the current record voids, or the trans that voids the current record_). ...I see more of an issue with using the username rather than an id value to reference them. – Uueerdo Jan 11 '17 at 18:13
  • @Uueerdo `locking` or `hiding` don't make clear the meaning, it like showing a state of the record. I think reading field name can tell you exactly what it is is a good practice, like `poster_id` ~> numeric field, `poster_name` ~> text field. Or if you mean `locking username` i think it comes with different meaning like this: `posted_date` vs `posting_date` https://community.dynamics.com/gp/f/32/t/106939 – TomSawyer Jan 11 '17 at 18:28
  • I choose `locked_by`, it's short but still has verb and passive meaning – Hieu Vo Jan 11 '17 at 18:32
  • @HieuVo I thought of it from the beginning. But `locked_by` can create the confusing: `locked_by` user_id or `locked_by` username? `locked_by` is a good option for the foreign key, not the text field. I already have a foreign key member_id / poster_id to the member table – TomSawyer Jan 11 '17 at 18:36
  • you should stick to 1 concept, id or username, and use it across your database, using mixed concepts is a bad practice. With only 1 concept, confusion won't happen – Hieu Vo Jan 11 '17 at 18:40
  • @HieuVo I don't think it's matter. One table can have few fields like this, Many software designed for hosting huge amount of traffic like vbulletin, phpBB, Xenforo ... they all store both id and text field for reference. If you use only id foreign key, you have to use join query for getting text field and it causes the memory so bad. Storing textfield is a wise choice while listing queries are the most resource consuming of every system. – TomSawyer Jan 11 '17 at 18:46
  • It depends on how we design the architect of software, for products which I worked on, they all use id as reference, to get more data from id, we implement a cache layer for it (memcached, redis, etc), it's much faster. Btw, it depends, there isn't absolute rule for every cases. – Hieu Vo Jan 11 '17 at 18:58
  • 1
    @TomSawyer I meant `locking` or `hiding` as prefixes, not whole field names; but I do generally prefer the past tense `locked_by_x`; it makes it clear the action was already done; `lock_by_x` makes it sound more like a directive rather than a current state, and even `locking_x` is ambiguous in that it can be interpreted as "this record locks x" instead of "x is the X responsible for locking this record". – Uueerdo Jan 11 '17 at 19:22
  • @Uueerdo Thanks for your advice. I picked `locked_by_x` at first but after certain times of researching, i came with `lock_by_x` , because it's shorter, make field names look consistent. I see many use `post_date` (noun-noun phrase) instead of `posted_date` (verb-ed-noun) along with `created_date`, `locked_date`, `closed_date` ... it may give you get close to the meaning but it's hard to remember while coding. – TomSawyer Jan 11 '17 at 20:52

2 Answers2

2

Whether your native language is Kiswahili or Klingon or English, what you want here are column names that describe current state. For example username_holding_lock means the column contains the name of a user holding a lock on the row.

Somebody using a tool to troubleshoot by doing SELECT * will then be able to guess what's up when the value TomSawyer appears on some row.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • So, you mean i should use present tense like `lock_by_username` , `post_by_username`? Naming the db field is like choosing one side but not both: consistence or meaning. I prefer: `post_username` but if i use `lock_username` or `hide_username` it sounds ridiculous – TomSawyer Jan 11 '17 at 18:22
  • 1
    I suggest avoiding *verbs* in column names. "locked" looks like a verb, but it's an adjective, – O. Jones Jan 11 '17 at 20:20
-2

I think this is more opinion based but I generally would use camel case. I personally would use: HiddenByUsername LockedByUsername

A good idea might be to look at some of the Microsoft sample DB's. A example would be the AdventureWorksDB or you can look here http://codeplex.com/SqlServerSamples

epelletier9740
  • 97
  • 1
  • 1
  • 6
  • fwiw, MySQL is generally case-insensitive with respect to column names. – O. Jones Jan 11 '17 at 18:17
  • Those are Pascal case, not camel case. Camel case would be hiddenByUserName and lockedByUsername. Also, given that this answer offers a tangential styling opinion and doesn't answer the question, it should be a comment and not an answer. – TiggerToo Apr 18 '18 at 15:07