naming of field convention

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
jaad
Forum Commoner
Posts: 95
Joined: Fri Jan 03, 2014 5:30 am
Location: Vancouver Canada

naming of field convention

Post by jaad »

Hello all,

I am at a point where I have no choice but to normalize my tables in mysql. I am new to mysql and php; just a few months and wanted to ask about your experience about the topic of naming database fields in mysql and manipulating this in php.

I come from a MS Access environment. I been using Access for many years; it is like badabing-badaboom for me. Mysql and php is a total new animal as far as I was able to see and before i get too fat with this I wanted to ask if you have any advices for me:

I will have roughly 80-90 tables to design. Many of the table will share a common name like address, name, phone, email etc... Do you guys find it easier to codename your fields according to the table where the data is stored or it is as easy to simply call all address fields the same `address` and then refer to it with longer sql and php code?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: naming of field convention

Post by Christopher »

jaad wrote:I will have roughly 80-90 tables to design. Many of the table will share a common name like address, name, phone, email etc... Do you guys find it easier to codename your fields according to the table where the data is stored or it is as easy to simply call all address fields the same `address` and then refer to it with longer sql and php code?
I think the first question I have is why there are many tables sharing common fields. It sounds like your database may need to be Normalized. Likewise saying you have "80-90 tables" raises some alarms for me, but I don't know what the application is. As for field naming, descriptive and standardized is good. There are also some conventions that many follow, such as naming primary keys address.id and foreign keys user.address_id.
(#10850)
User avatar
jaad
Forum Commoner
Posts: 95
Joined: Fri Jan 03, 2014 5:30 am
Location: Vancouver Canada

Re: naming of field convention

Post by jaad »

I think the first question I have is why there are many tables sharing common fields.
well, my clients have addresses, their clients have addresses, suppliers have addresses, tenants have address, their employers have addresses, their emergency contact have addresses, past addresses with other landlords.... do you want me to go on? the reason why I have so many tables is exactly because of that reason, my tables are normalized.

What do you mean by descriptive and standardize? something like tenant_address, Supplier_address, Client_address... something like that? This is the system I currently use. it works great in MS Access but just wanted to make sure it also work good while building an application for the web.
jkon
Forum Newbie
Posts: 16
Joined: Mon Feb 15, 2010 6:01 am

Re: naming of field convention

Post by jkon »

The address is more complicated to talk about, it is an object in real life (it has postal code , street name , country …. And more ) in OOP and should be a row in a table in a db and then use that id in other tables , eg the supplier table should have an address_id on it , meaning the id of the row in the table addresses. But lets get to a simpler example , name. A supplier has name , a employer has name also. You don’t have any reason to name the field supplier_name , you can just call it name , it is in the suppliers table so there isn’t a way to be confused in a query with the name that is in the employers table.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: naming of field convention

Post by Christopher »

jaad wrote:well, my clients have addresses, their clients have addresses, suppliers have addresses, tenants have address, their employers have addresses, their emergency contact have addresses, past addresses with other landlords.... do you want me to go on? the reason why I have so many tables is exactly because of that reason, my tables are normalized.
Wouldn't it make more sense to have a field to the type of contact and a field for the parent of the contact.
[text]id type name parent_id address phone ...
1 I Me 0
11 C My Client A 1
12 C My Client B 1
13 S Client B Supplier 12
13 T Client B Tenant 12
[/text]
If the relationships become many-to-many then introduce a link table.
(#10850)
Post Reply