When I design a database i prefer to stay away from certain table names and certain column names so in general i would avoid using a column name of id or value etc.
on the same subject I avoid using duplicate names in different tables, especially where those tables might ever be joined to form a larger query, so I do not name things like tel, phone, fax, email etc.
If I gave a simple example lets say I have a video store called Pauls Videos and I wanted to keep track of all videos out and returned, customers, accounts, etc. I will just give a simple example.
Table 1 Name: pv_movies - I prefix with pv for pAULS vIDEOS
pv_movies Column Names: pv_m_id, pv_m_release_year, pv_m_movie_title - I add prefix m for mOVIES
This way i ensure i never have any duplicates and never use any name that might be designated for something else. It also means I never land up joining multiple tables and having to alias a whole bunch of column names.
My question is, is the way i do this, clever, dumb, or just unnecessary, if I had a table called id, or a column called id and i have multiple tables all starting with the PK being called id, is that wrong, right, will it cause trouble down the line.
I always prefix primary key fields, calling them all id or similar is a recipe for disaster in my mind. I do sometimes duplicate field names but also in many cases live to regret it!
I think it is really as case of what works for you, don’t be influences by so called experts who often are not. (that doesn’t include me - lol)
Haha haha. I always include you as my go to database guru. Thanks for the answer. To be honest I have done it this way for so long I can hardly remember why. I think it was Tod Perkins on Lynda that said do it like this 1000 years ago or something.
EDIT I started learning mysql in 1999. Almost 20 years later and only now I am asking this question. So whoever said it must have been very convincing. Like when Patrick says jump 3 times to make your site work. Agree or not, you just do it.
Very good advice. Have seen a lot of debates over this exact question. Have also spoken to a lot of DBA's about this as well. Non seem to stick to any identifiable standard. I'd say always check your queries performance. There are lots of tools that allow you to do this. Then keep tweaking them. Aliasing anything is always a terrible thing to do. Large tables are never good. Stick to as many 0/1 variables as possible. Never use Admin or password in any column names. Never have your administrator with a low id/user_id, or any high ranking User as a low AI/PK.. Yada-yada-yada....
As for naming structures try to be obscure if you're concerned about security (but that doesn't matter if your server is rooted).. For simplicity stick to what you're familiar/comfortable with.
Thanks @Dave good advice. I’m glad I got info from you and Brian. Although I am especially glad I have not been doing it all badly for 20 years, that would have been really sad. Lol.
Never thought about the security aspects much so that is new news to me and makes perfect sense. Very clever.
No worries Paul… One more thing I would say, another security thing. If your DB User does not need Super User Privileges don’t assign them. Separate your DB Users. For simple Users allow only basic DB permissions. For your Administrators use a different DB User with the higher privileges/Triggers/Procedures etc. So many breaches are down to poor User rights management it is ridiculous!
Sorry, late to the discussion once again. Being in an earlier time-zone to you guys ensures that I am fast asleep while you guys are churning your thoughts.
Well here goes mine, I would call the movies table 'movies' and the table fields would all be prefixed using 'movies', like moviesID, moviesTitle, moviesDescription etc.
This also works well when using the table/form generators in Wappler, the headings/lables will automatically be translated into Movies ID, Movies Titles, Movies Description etc.
I too follow a similar naming convention for database tables.
And a few posts ago, @ben also suggested to prefix foreign columns with table name and it has made a great improvement while using these fields in Wappler or otherwise.
I knowingly avoid underscores in naming all things. Its just takes me a lot of time to press shift +_ rather that shift+alphabet when using camel case. And, for case-insensitive queries I can even skip writing column names in camel case.
And, I have learned my lesson from the days of using just ID for PK, and never repeat that mistake.
