hi I am wondering whether there is any advantage of using id as a primary key in a users table, versus something more descriptive like userid or user_id.
Imho the red bit is subjective and I accept many people feel the same way.
But I was taught to use the Hungarian Notation System (or whatever its actual name is) and I can see merits in it and that is what I am used to using.
I also use it (or my version of it) to name html form elements - eg textboxes are prefixed with txt, selects lists are prefixed with sel, labels with lbl etc etc.
I think what naming convention you use is not so important as long as it is clear. What is important, imho, is that you use a naming convetion consistantly throughout your code and not mix and match naming styles/conventions because that would make your code really hard to read for anyone else looking at your code.
btw - your second suggestion is… 
first suggestion (and it’s a pretty strong suggestion) – lose the “tbl” in your table names, and the “fld” in your column names
sql is a strongly typed language
you cannot use a table name where a column name should be used, and vice versa
there is no point in making your names harder to read
i disagree
use id as the PK, and foobarid as the FK
using the same name because it’s the same data is a slippery slope
there are actually two separate issues – whether to use a surrogate key, and what to call it
the surrogate key would be an auto_increment, where the user is identified by a number, as compared with the user being identified by a userid like ‘BigTodd’
my own preference is never to use “id” as part of the column name unless you’re dealing with a surrogate key
so in the previous example, ‘BigTodd’ would be called username, and it would still be a perfectly good primary key
when i use an auto_increment, i always give it the name “id” (so that i know that every table that has a column called “id” is using an auto_increment as the primary key)
then foreign keys in other tables are called “userid” if they reference the id column in the users table
exactly
and, on a somewhat related topic, i insist on the “[table].[field]” syntax (although i would use “column” instead) whenever a query involves more than one table
as a direct consequence of this, the convention of stuffing the table name into the front of the column name (as in ord_custname, ord_total, ord_shipdate) is redundant
which means that you name your columns custname, total, and shipdate
of course, if a query involves only one table, then the “[table].[column]” syntax is unnecessarily wordy, and i would then drop the table qualifier
I would use userId in a table of users, prodId in a table of products and catId in a table of categories.
If they were all named just id it could make the readability of sql queries a little alkward - just my :twocents:
user_id in a table named users is repetitive when “id” is more then enough to convey its purpose.
Now when used in another table as a forgien key that is different.
Just make sure that you don’t have fields in other tables that use the same field name for something different. Having the same field name mean the same thing across the entire database makes a lot of things a lot simpler.
You could use the “[table].[field]” syntax if you are having readability problems.
ok so it seems both are recommended, and both are good.
You mean the name of the column? It’s a bit shorter.
Yes I could and do when the same column name exists in more than one table - eg catId in the category table and catId in the products table. But I prefer the primary key name to be specific to its table.
Maybe I’m not explaining myself clearly.
Here is an example of what I do.
Table name: tblcategory
Column
fldCatId - primary key
Table name: tblproducts
Column
fldProdId - primary key
fldCatId - foreign key
tblcategory has a 1 to many relationship with tblproducts.
yes I agree it is not mandatory for databases and most probably for anywhere else for that matter.
But when using data like for example
$row['fldProdName']
$_GET['txtUsername']
$_GET['selCatId']
I prefer to use this type of notation as it makes it perfectly clear to me where the data is coming from.
Please don’t misunderstand me. I am not saying this is the only or best way of naming things. It is just the way I do it.
I said before, it is more important to use a consistant naming convention throughout your code. Whether that convention makes it easier or harder to read for anyone else looking at your code is totally subjective.
There are no merits whatever in using it for names inside the database and many many reasons to NOT use it there. Whoever taught you to use that obviously didn’t make it clear enough to you that it doesn’t apply to databases.
That means you can’t use the same name for your foreign keys which will make your naming a real mess. The primary key name is the one that is almost guaranteed to be needed in other tables as a foreign key where you should use the same name since it is the same data.