DataBase naming... Any Conventions?

[Cableguy]

Hi Guys!

So I need to create a MySQL DataBase that will be hosted, for time being, on my "low-end" VPS...
Eventually, as the project will reach completion, it will be migrated to a more "mid-end" VPS.

So, I always struggle finding good names for my projects, specially if they do not have a very catchy specificity...
How do you guys go about naming your Projects/DataBases?
I was thinking using a password generator for ambiguous naming ...

 

[nwhitfield]

I just go for the blatantly obvious and simple, especially since I tend to end up over time with various versions of sites and their associated databases. So, for example, the main site I work on, called BLUF, is presently in its fourth incarnation, and the MySQL database is called BLUFv4. Another site, called stimbroker has a database with the same name, and for another site, I have the current iteration named itpaV3 and the previous one itpaV2 is still hanging around. So, I'd say, just call them what you want.

If you want to keep things simple, name them like packages, eg com.mycompany.myproject so you can at least see what project they are for easily.

I'd personally strongly advise against using a password generator, because a) you'll curse yourself one day when you're looking at your list of databases in MySQL trying to remember which project uses the database called P34Ed9wkyqUP, and b) you'll very quickly become tired of having to copy and paste it whenever you need to write some code that references the name.

That said, to an extent the database name is largely irrelevant, because other than when you set up the connection to it, you can assign it to whatever easy to remember variable name you want in your code, whether that's server side in php or in B4x when you're dealing with a local database file. It's probably more important to make sure the tables are clearly named, with titles that will help you to remember what's in which table when you're writing queries in six months time.

 

[Cableguy]

Thanks for your input, I tend to over-complicate things

 

[eps]

The database name isn't the hard part - it's making sure all the primary keys and foreign keys etc.. are easily identifiable!

The easiest is pk_ and fk_ but it can start to get a bit complicated

 

[Lahksman]

I use another method for my primary and foreign keys.
For instance i have a table Game and Gamedetails.
PK Game = GameID
PK Gamedetails = GamedetailsID
FK Gamedetails -> Game = GamedetailsGameID

Downside of this method is that column names can get rather long.

 

[Cableguy]

Hummm I need to better understand PKs & FKs and their usages... Thanks for all the input

 

[sorex]

I don't follow the fp/pk naming either that you see in courses.

If you are the only one who works in the database or it's structure you know what it all is.

using the IDs at the end of your field as Lahksman mentioned is a hint that it is a reference to another table.

 

[Adie]

I use a simplified Hungarian notation on variables and objects. https://en.wikipedia.org/wiki/Hungarian_notation Most development is done in C++ Builder where everything is case sensitive. As I just started with B4A so dont know about case sensetivity yet.

On database names I use all lowercase tablenames and the fields will be the same as the object in the program. All lower case for fields and rigid mix case for objects (stock_nr/Stock_Nr and mix_nr/Mix_Nr) This way there are no ambiguity about and less to remember. I never use any FK key in my MySQL databases as I design the database management code to be 'bulletproof' (I use buffers between the database and the user so also never need to rollback etc etc.) Actually I (almost) do not need PK/INDEX as the app validate unique data during user entry where time is not an issue. The database (more than 2Gb) can get to over 500 000 records on some tables and show no speed degradation.

Adie