Page 1 of 1

db->table->column convention question

Posted: Fri Oct 30, 2015 8:35 am
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!

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 9:07 am
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.

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 9:26 am
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

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 9:33 am
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.

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 10:50 am
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?

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 11:07 am
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)

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 1:07 pm
by diseman
Thanks Celauran.

What did you think about my original Q4?

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 1:17 pm
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.

Re: db->table->column convention question

Posted: Fri Oct 30, 2015 2:30 pm
by diseman
Thanks again Celauran. Going to rebuild db now.