db->table->column convention question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

db->table->column convention question

Post by diseman »

hi coders,

Trying to learn PHP and nail down this db->table->column practice, but can't find anything on the Internet where anyone talks about it.

My fictitious Users tbl:

user_id, user_type, username, first_name, last_name / (user_id is auto-increment)

My fictitious family tbl:

family_id, user_id, username, first_name, last_name / family_id is auto-increment)


Originally: when I post a record to the users tbl, I post an empty record into the family table to keep the id's the same. I'm doing that because I remember at work, when looking at a db, I used to get frustrated with the tbls trying to match user_id numbers. Just seems easier to have matching id's and even an email address and/or name in each table.

moving on...

Q1. If I insert a blank record in a tbl to keep rows evenly numbered, do I have to have the USER_ID in the family tbl? Seems like I could leave it out and the user_id will be the same as the family_id.

I feel as though this would work, but is ultimately wrong convention. I'm not experienced enough to know all the possibilities, but feel like something could come up down the road that would make this convention very wrong.

Q2. So, if that isn't good -- should I have family_id auto increment and add a user_id that matches the users.users_id?

Q4. Finally, and last question, what are your thoughts about adding username (email addy) and/or first/last_name to each table for ease of viewing sake?

Interested in your thoughts!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: db->table->column convention question

Post by Celauran »

My primary key for tables is almost always id, and almost always a UUID. foo_id represents a foreign key relation. I think in terms of relations between the data when I design my tables. A user belongs to one family, a family can have many users, so it's a pretty clear one-to-many application. I would therefore structure my tables something like:

Code: Select all

users
 - id
 - family_id
 - username
 - password
 - first_name
 - last_name
 - ...etc

families
 - id
 - name
 - ...etc
Where users.family_id is a families.id

This keeps any given piece of data in a single location, which is hugely important. There's no need to try matching IDs across tables, just use these relations.
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: db->table->column convention question

Post by diseman »

Hi Celauran,

Didn't see that coming.

So, if I had another tbl called CARS, would you add another foreign key to the users tbl called CARS_ID like this:

users
- id
- family_id
- cars_id
- username
- password
- first_name
- last_name
- ...etc

families
- id
- name
- ...etc

Cars
- id
- make
- model
- ...etc
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: db->table->column convention question

Post by Celauran »

It would probably go the other way; the car belongs to a person, a person doesn't belong to a car. My cars table would have a user_id column linking it to the user who owns the car. Foreign keys also tend to be singular as they refer to a specific row rather than the whole table.
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: db->table->column convention question

Post by diseman »

Celauran wrote:It would probably go the other way; the car belongs to a person, a person doesn't belong to a car. My cars table would have a user_id column linking it to the user who owns the car. Foreign keys also tend to be singular as they refer to a specific row rather than the whole table.

Ok, so if cars tbl has user_id back to users table, what column is in users tbl you're linking to; user_id?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: db->table->column convention question

Post by Celauran »

Would be users.id in my example, but it's always that table's primary key. I can then easily get all cars a user owns

Code: Select all

SELECT * FROM cars WHERE user_id = ?
or get a given car's owner equally easily

Code: Select all

SELECT * FROM users WHERE id = ?
(Where the ? represent bound parameters; the user's ID in the first instance, the car's user_id value in the second)
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: db->table->column convention question

Post by diseman »

Thanks Celauran.

What did you think about my original Q4?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: db->table->column convention question

Post by Christopher »

diseman wrote:Q4. Finally, and last question, what are your thoughts about adding username (email addy) and/or first/last_name to each table for ease of viewing sake?
I would recommend not replicating data. Do some research on normalized database schema.
(#10850)
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: db->table->column convention question

Post by diseman »

Thanks again Celauran. Going to rebuild db now.
Post Reply