db->table->column convention question
Posted: Fri Oct 30, 2015 8:35 am
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!
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!