Joining multiple tables

[johnphpnewb]

I am trying to finish my website soon. I’ve been thinking about security and i’ve noticed that alot of websites fail to notify users of possible security breeches at various levels. For example, i use “desktop” background photos which can be changed by the user. Today i was thinking that each photo should receive a random name (random_bytes(8)). Imagine the account was hijacked and the suspect changed the background, then reset the original background to cover tracks. It wouldn’t work because the name of the background will be different, thus the account holder will know something is screwy.

my point is that i wonder how i should store this data. For example, Google Mail has the last twenty login datetime stamps for an account. I assume that this is stored in a database. I am not a database designer, so i am unsure how to correctly build a database to store all of my user data.

i resist the idea that one should have all of the data in the same table. For one thing a login would be ridiculously lengthy if the table has 20+ columns. I do not think that a table should have so many columns but i don’t know how to link multiple tables with a join.
i would like to use a last login archive table, a bookmarks table and a background photo names table (with possibly more based upon security ideas that i may have in the future.)

so far, i understand that a database should be ‘relational’ for this concept. So table 1, table 2, table 3, table 4 should be related somehow. Is this relation best accomplished by a common column other than an auto-increment id?

i’m not asking for code but a general tip as to how this works in relation to what i am trying to accomplish.

Thank you.

[astonecipher]

Future reading, Database Design Mere Mortals.

The simple answer is, the answer is too complicated to answer LOL.

As far as security goes,
Don’t count on a user to care about a filename, or notice what it is/ was.
I don’t know what or why a hacker would change a background photo. If they are defacing your site, they aren’t going to change it back; and if they are after data, the goal is to go unnoticed anyway.

Databases can be encrypted at rest and data can be encrypted in transit.

You need to figure out what you want to store and how it relates. Then you can work on the relations.

[johnphpnewb]

Thanks for replying. Seems that i have a bit of planning to do…
I forgot that i have some older books about mysql db design. I will read.

Don’t count on a user to care about a filename

that is no excuse to leave a single stone unturned. most people don’t care about alot of things until something happens. Modern security means treating your site like a victim of a crime scene, except you have control of the entire event.

Meantime, i’ll get out my books and try to understand this better. I all ready know that you are a superb db designer, so i will take your advice and start reading…

Thank you.

[astonecipher]

If you had more info on what needed to be stored, I could give a better idea; but overall you need to figure out whether those relationships are one to one, one to many, or many to many. That will give some determination on needing a foreign key in a table verses a linking or joining table.

[johnphpnewb]

my db is messy and i’m still trying to discover what is needed for my site to be complete.

so far, i have a user accounts database which holds the following data:
id, username, passwordhash, login_session_id, login_datetime, last_login_datetime, ip_address, user_agent, display_name, bg_photo, bg_photo_tint

i intend to add a new table tentatively named user_bookmarks which contain columns:
id, link_text, link_url, link_image

i also wish to add a last_login_datetime archive, we could call it login_archive:
id, login_datetime, ip_address, user_agent

i will continue reading about database design. I am an absolute beginner with this subject. I have minimal experience here. In fact, i just learned pdo at this forum a few months ago with the help of phdr (Thank you so very much, Sir).

edit: i also have a few other new entries in my user table: login_lock, multi_session_violation, multi_session_token, pass_reset_token

[astonecipher]

id, username, passwordhash, login_session_id, login_datetime, last_login_datetime, ip_address, user_agent, display_name, bg_photo, bg_photo_tint

these can be split
user:
id, username, passwordhash, display_name

sessions:
login_session_id,

logins:
login_datetime, ip_address, user_agent

user_settings:
bg_photo, bg_photo_tint

[johnphpnewb]

Good morning and Thank you, Sir.

I will alter my tables today and organize them as you have illustrated. I’ve neglected the db design so that i can focus on the site design and php code but now the db is really messy and unprofessional. I will focus on this side of backend.

I use the session_id to check for multiple sessions. Thus, at successful login (username and password match), i elevate with session_regenerate_id(true) (and yes, i store the old session id to deal with network lag and an obsolete flag). Then, i insert the session id into the table and i set a session variable to new_login 1. If new login, then do not check database (because we’ve all ready done that at login.) After a new login, then every page request checks the database for a matching session id, else multi-session is true. Then, i can think of how to use ip and user-agent fields to determine if the user is possibly different but this is unreliable so i have yet to do something about it other than log it. Anyway, then i will set a multi-session column to true so that one cannot end the session to thwart the lock. All subsequent logins will show the same screen: multi-session detected please verify your account with the token sent to your registration email sort of solution. Enter the token to unlock the session and continue using the site. This will be easier to track by using a separate table.

[johnphpnewb]

also, now the picture becomes clearer to me with your illustration: i could just create a last_login table which is all ready an archive. I think sql has a last update feature, so i could use that to display your last login. Thank you, astonecipher. This is why i like organized and structured code: i can think clearer because the picture is clearer.