Primary Key naming convention

[SaranacLake]

Hello.

So I have spent all night - plus another day last week - pondering the naming conventions that i want to use for my database.  And I think I have a solid approach.

But before I was ready to nod off, I wanted to ask this additional question...

As I recall, when you create a table in phpMyAdmin, it assigns the name "PRIMARY" to your primary key.  (I'm pretty sure it is phpMyAdmin doing this, and not MySQL.)

So anyways, I don't really want to use that naming convention.

Rather, I would like my primary keys to use this format...

	tablename_pk
	

 

So, if I create the table using SQL and I name the primary key as I would like, will this cause any issues with MySQL or phpMyAdmin?

I'm pretty sure the answer is, "No", but you never know?!

Thanks!

 

[requinix]

46 minutes ago, SaranacLake said:

(I'm pretty sure it is phpMyAdmin doing this, and not MySQL.)

Guess again.

 

46 minutes ago, SaranacLake said:

So anyways, I don't really want to use that naming convention.

Too bad.

 

46 minutes ago, SaranacLake said:

Rather, I would like my primary keys to use this format...

Doesn't matter. Nobody cares what the names of your indexes are as long as you didn't manually name them something stupid.

[kicken]

The MySQL manual says:

Quote

In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique.

 

So it would seem you are stuck in that case.   I generally use SQL Server these days which does allow the name to be specified and I use PK_<tablename>.   I use a similar format for other constraints, just with different prefixes/suffixes.  How you name them doesn't really matter though, many people just use auto-generated names and it works just fine.

 

[SaranacLake]

9 hours ago, requinix said:

Guess again.

Too bad.

Doesn't matter. Nobody cares what the names of your indexes are as long as you didn't manually name them something stupid.

Any reason to be so nasty lately?

It's a legitimate question, and a damn good idea to organize everything in your database.

[SaranacLake]

2 hours ago, kicken said:

The MySQL manual says:

So it would seem you are stuck in that case.   I generally use SQL Server these days which does allow the name to be specified and I use PK_<tablename>.   I use a similar format for other constraints, just with different prefixes/suffixes.  How you name them doesn't really matter though, many people just use auto-generated names and it works just fine.

  

2 hours ago, kicken said:

The MySQL manual says:

So it would seem you are stuck in that case.   I generally use SQL Server these days which does allow the name to be specified and I use PK_<tablename>.   I use a similar format for other constraints, just with different prefixes/suffixes.  How you name them doesn't really matter though, many people just use auto-generated names and it works just fine.

Oh, so it is MySQL that is being a pain, and not phpMyAdmin.

Hmmm...  I was certain that you could rename the primary key to what you wanted, but i guess that is not the case?!

Well then, I guess this is a moot point.

Too bad, because I would prefer to use my naming convention.

And, yes, I know you can let MySQL auto-generate everything, but then it becomes a real PITA to search for and manage things.  (Only a lazy person would not organize index and constraints in his/her database!)

 

[gizmola]

There are some things you need to name yourself, so having a standard way of doing it, isn't a terrible idea.  The counterargument to your concerns is that you don't need to organize anything, because the data dictionary already maintains the relationships between these database objects.  If you want to find something, you will be able to by querying the data dictionary.

[SaranacLake]

12 minutes ago, gizmola said:

There are some things you need to name yourself, so having a standard way of doing it, isn't a terrible idea.  The counterargument to your concerns is that you don't need to organize anything, because the data dictionary already maintains the relationships between these database objects.  If you want to find something, you will be able to by querying the data dictionary.

Why take the lazy way, when you can be more detailed?

https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/#explicit-naming

 

I agree with nearly everything this guy says on his webpage...

 

Indexes should be explicitly named and include both the table name and the column name(s) indexed. Including the column names make it much easier to read through SQL explain plans. If an index is named foobar_ix1 then you would need to look up what columns that index covers to understand if it is being used correctly.

 

Edited May 21, 2020 by SaranacLake

[gizmola]

Those are reasonable standards, but you have to keep in mind that postgresql and mysql have significant differences.

With Mysql/InnoDB there is no difference between keys/indexes and constraints.  Of course constraints have optional on update and on delete configuration, but in terms of the underlying mechanics of indexing, an fk constraint creates a corresponding index.

I don't want to get into re-writing the MySQL manual, but there are now check constraints in the latest versions of MariaDB and MySQL I really haven't used, but I only mention this for completeness.

The only way you can name an index or constraint is to create it separately from your create table statement via alter table. 

So you'll need to decide if naming things via a convention that is different from the defaults is important enough for you to have to break up the activity of table creation into multiple create and alter statements, and of course keep track of this fact should you need to make changes during the development phase and in the future, which in my experience is inevitable.

[SaranacLake]

12 minutes ago, gizmola said:

Those are reasonable standards, but you have to keep in mind that postgresql and mysql have significant differences.

With Mysql/InnoDB there is no difference between keys/indexes and constraints.  Of course constraints have optional on update and on delete configuration, but in terms of the underlying mechanics of indexing, an fk constraint creates a corresponding index.

True.

 

12 minutes ago, gizmola said:

I don't want to get into re-writing the MySQL manual, but there are now check constraints in the latest versions of MariaDB and MySQL I really haven't used, but I only mention this for completeness.

Yes, I see that.  Sadly, cPanel is being lame and only offers MySQL 5.7 so it will be at least until later this year before i could ever take advantage of them.  So for version 1, those are out.

 

12 minutes ago, gizmola said:

The only way you can name an index or constraint is to create it separately from your create table statement via alter table. 

So you'll need to decide if naming things via a convention that is different from the defaults is important enough for you to have to break up the activity of table creation into multiple create and alter statements, and of course keep track of this fact should you need to make changes during the development phase and in the future, which in my experience is inevitable.

You can actually name FK's and Indexes in phpMyAdmin - maybe it breaks things up into two steps as you mention?  I don't know.  (This would be another benefit of writing my own DDL's, is that I learn and see how things work.  Although there is a good chance I';; just use phpMyAdmin for speed right now.)

 

[gizmola]

3 minutes ago, SaranacLake said:

You can actually name FK's and Indexes in phpMyAdmin - maybe it breaks things up into two steps as you mention?  I don't know.  (This would be another benefit of writing my own DDL's, is that I learn and see how things work.  Although there is a good chance I';; just use phpMyAdmin for speed right now.)

I thought you were planning to write the DDL.  Clicking away at phpMyAdmin like a monkey is about the least efficient way to do this I can think of.  You were arguing to me that you didn't have time to learn how to use mysql workbench and make an ERD!  This is the type of response that you sometimes use to rebuff advice, which leaves many of us with the impression that you rationalize your predispositions.  

You absolutely should have a full sql script with all the DDL to create all the tables, and constraints.  

[SaranacLake]

13 minutes ago, gizmola said:

I thought you were planning to write the DDL. 

I said I might...

 

13 minutes ago, gizmola said:

Clicking away at phpMyAdmin like a monkey is about the least efficient way to do this I can think of.

Kind of like clicking around like a monkey in Workbench, huh?  😉

 

13 minutes ago, gizmola said:

You were arguing to me that you didn't have time to learn how to use mysql workbench and make an ERD!  This is the type of response that you sometimes use to rebuff advice, which leaves many of us with the impression that you rationalize your predispositions.  

Seriously?

Let's see...

I know how to use phpMyAdmin.

And I know how to write SQL.

Yet I do NOT know how to install or use Workbench.

So, yes, I said I would likely hold off using Workbench because I do NOT know it, and I do NOT have the time to learn how to use it properly.

I am not "rebuffing" advice - you're not paying attention to what i said.

 

If it was not possible to name or rename constraints and indexes in phpMyAdmin - and I actually didn't know you could - then it would still be easier to do it creating my own DDL's and running it in the SQL window versus learning a new GUI.  But since it appears I can do it in a tool that I already know, that seems the way to go.

 

Sorry, but I am not following what your concern is...

 

13 minutes ago, gizmola said:

You absolutely should have a full sql script with all the DDL to create all the tables, and constraints.  

If I use phpMyAdmin or learned Workbench, I wouldn't need to because the GUI does it for you.

 

 

[gizmola]

My point with phpMyAdmin is that it is a tool that hides all the details from you.  So yes you can go in and make a table, add indexes, relationships etc., table by table.    Than you can go in and add things.   The expectation of the tool is that its for people who don't really know SQL.  It's ubiquity is largely a function of the fact that it's been around a really long time.  The basic ingredients are:

• 1st time creating a table = CREATE TABLE
• Changing something = ALTER TABLE

But neither of these is a script you can play and replay.  The time it will take you to make all the tables with phpMyAdmin is guaranteed to be equal to or greater than the time to make an ERD in workbench, with none of the quality, and no ERD diagrams to look at when you're done.  

I refer you to the other thread on foreign keys and the subtype.  Having read your post:

• I Installed Workbench on my mac
• ran it for the 1st time ever
• Made a model of the tables (click type click drag)
• Related the tables (click drag)
• Generated the diagram
• generated the SQL I put in the thread

Took me about 15 minutes or so, and I was doing this while talking to my kid.

I have never used Workbench to make a diagram ever before.  There was one non-intuitive thing I had to figure out but overall it was a lot more functional than I expected.  There are things I've mentioned like domains, that I probably would want to use that I didn't attempt to figure out in it, but again I spent absolutely no time learning it.

Now I grant you that I've used about 5 different commercial tools over the years, so I know what I'm looking for, but I just don't believe it would take you that long to figure it out.  I'm also sure there are probably 50 video tutorials on the basic of it on Youtube.

Also FYI these tools including workbench have a reverse engineering feature where they will read either a live db schema or a db dump and bring the existing model into the tool.

Again, I'm beating a dead horse here.  I think you've built these things up in your mind.  Using workbench to digitize your diagrams is not the same as becoming a master of photoshop.  

I will do my best not to bring it up again 

 

 

[SaranacLake]

21 hours ago, gizmola said:

My point with phpMyAdmin is that it is a tool that hides all the details from you.  So yes you can go in and make a table, add indexes, relationships etc., table by table.    Than you can go in and add things.   The expectation of the tool is that its for people who don't really know SQL.

Which was my earlier point of the merits of writing my own DDL's in SQL...

 

21 hours ago, gizmola said:

  It's ubiquity is largely a function of the fact that it's been around a really long time.  The basic ingredients are:

• 1st time creating a table = CREATE TABLE
• Changing something = ALTER TABLE

But neither of these is a script you can play and replay.  The time it will take you to make all the tables with phpMyAdmin is guaranteed to be equal to or greater than the time to make an ERD in workbench, with none of the quality, and no ERD diagrams to look at when you're done.

For you, I don't disagree.  For me, I do disagree.  Some day once I have mastered Workbench, then I too can do as you say.

 

21 hours ago, gizmola said:

I refer you to the other thread on foreign keys and the subtype.  Having read your post:

• I Installed Workbench on my mac
• ran it for the 1st time ever
• Made a model of the tables (click type click drag)
• Related the tables (click drag)
• Generated the diagram
• generated the SQL I put in the thread

Took me about 15 minutes or so, and I was doing this while talking to my kid.

I have never used Workbench to make a diagram ever before.  There was one non-intuitive thing I had to figure out but overall it was a lot more functional than I expected. 

You know, I really appreciate all of your help, and I don't doubt your years of knowledge, competency, and so on and so forth.  But I also think sometimes you get a little shortsighted...  😉

On our design debate, while I can see most of your points, I'm not sure you can see things my way.  And again, here, I think the same things is happening...

One of the first rules of being a good teacher is not insisting that your pupils see the world as you do, and have the same experience that you would have doing something, but rather to help them reach THEIR potential.

I know that "in theory" it sounds all so simple to install and use Workbench, or a PHP framework, or MVC, or OOP.  And for you these things are likely easy.  But I am not you, and you are not in my shoes...

You have no clue the things I am facing right now in life, nor can you begin to understand all that is on my plate.  Trust me.

If keeping my head above water was my primary goal, I would throw PHP/MySQL and my website in the trash and focus on my important things.  But I have come so far, and the finish line is in sight, and I am "playing the long game" and not just trying to survive this week, but have a better life down the road.

And I am eager to master many of the things you talk about, but now is not the time.  (If that still doesn't make sense, it's because you're looking at my problems through YOUR eyes and not through MY eyes...)  😉

 

 

21 hours ago, gizmola said:

There are things I've mentioned like domains, that I probably would want to use that I didn't attempt to figure out in it, but again I spent absolutely no time learning it.

Now I grant you that I've used about 5 different commercial tools over the years, so I know what I'm looking for, but I just don't believe it would take you that long to figure it out.  I'm also sure there are probably 50 video tutorials on the basic of it on Youtube.

If I thought Workbench was a better approach right now today, I'd be using it.  But it isn't.  If I am still alive (and not out on the street) come August, I will pick it up.

 

21 hours ago, gizmola said:

Also FYI these tools including workbench have a reverse engineering feature where they will read either a live db schema or a db dump and bring the existing model into the tool.

Right.  This I know.

 

21 hours ago, gizmola said:

Again, I'm beating a dead horse here.  I think you've built these things up in your mind.  Using workbench to digitize your diagrams is not the same as becoming a master of photoshop.

But it is an additional distraction and stressor that I don't have the bandwidth to handle, today.

 

 

21 hours ago, gizmola said:

  I will do my best not to bring it up again 

I appreciate the nudges and you playing "devil's advocate" - for anyone who truly cares about me, I *expect* that.

But I also know ME better than anyone on the planet Earth.  And I know my current situation, and you don't. AND, being an analyst (formerly) by day, I think everything I do out more than anyone you'll meet, and I stand behind every decision I make.

So much of your advice is duly noted, and will be great for v2.0, but right now I am focused on eating each day, and shipping a competent v1.0 of my website...  😉

 

 

[gizmola]

On 5/22/2020 at 7:15 PM, SaranacLake said:

One of the first rules of being a good teacher is not insisting that your pupils see the world as you do, and have the same experience that you would have doing something, but rather to help them reach THEIR potential.

This is not a teacher - student situation.  As it happens, I have a good friend who is a college professor, as is his wife, and we talk about his classes and students fairly regularly.  I know from his point of view, having standards that students have to meet is very much part of the job. There are assignments and tests.  None of this is part of our discourse.  In general, one of the most important elements of a community like this is the exposure to new ideas, technologies and methodologies, as well as exposure to a consensus on a baseline of competency.  

Your replies frequently seem to me to have elements of both FUD and NIH.  I'm guessing you are familiar with both acronyms.  I understand that you are concerned about what you don't know, and are unsure in regards to your ability to learn things that are new to you in the short and long term, but these topics did not come up by happenstance.  You posted questions soliciting feedback, which is what you've received. 

On 5/22/2020 at 7:15 PM, SaranacLake said:

And I am eager to master many of the things you talk about, but now is not the time.  (If that still doesn't make sense, it's because you're looking at my problems through YOUR eyes and not through MY eyes...) 

In all the replies and feedback, I don't know if there's an instance where someone gave you an answer or opinion you've agreed with, that wasn't yours to begin with.  It feels to me like an echo chamber, where you only want people to tell you that you were right from the get go.  You seem to want to reduce this to a matter of opinion amongst equals, when that just isn't the case.  I've done system architecture and engineering for my entire career.  I know what's important, and I've tried to help you understand what that is, in dribs and drabs. 

I could at this point make a nice list of all the things you've disregarded, along with various excuses for why you know better than me what you need right now.  At the end of the day, if we listed the requirements for what you want, what you've already built and how they can be integrated, sans a serious discussion of whether your requirements are sane or logical, or even prioritized correctly, there's a best practices way to build something new, and there's a way to make spaghetti, that will likely be a costly bug ridden failure.  

What the experienced practitioners here all know, is that doing it the right way isn't more difficult, nor does it take longer.  Quite the contrary in fact.  Doing it the right way produces a better product in less time because it's based on the assembly of pre-built and tested pieces, that more often than not have documentation and support.    

Again, I do this to try and help others, as do the other respondents who frequent this site.  Some percentage of people find what they are looking for and some percentage do not.  Most of us have been participating in this forum for many years.  We've seen every type of question and questioner there is at this point.  You might want to consider that when you question the relevance or urgency of the advice you've received.