Im building a lage scale project that will have thousands of users. It's a online portfolio system but Im gonna try make it simpler to understand using "houses" instead portfolios.
So far, I have two tables:
one called "houses" with many columns.
and another one called "users" with same number of columns.
each user has one house and each house can be owned by one user.
I use "house" table quite a lot but i dont use "users" much.
I have the data that is less used such "user name", "second name", "max stay", "DOB", and other stuff like that to users. This fields could go to the table houses cause the 1 to 1 relationship but maybe is faster like this?
Also the ID of each house is a string name not a proper Numeric ID. So "users" has the foreign key "id" from houses. I'll be searching on the table users just sometimes but houses is used all the time where i have to read and edit some data from there. There will be thousands of rows.
Now the questions are:
Shall I put all of that data in the same table?
Shall I leave it as I have it?
Shall I use numeric IDs on the houses table and use that ID to link both tables?
If I do then I have to search on "houses" table for the real ID and then move to the "users" using that ID that i just found. Or maybe I should work just with IDs?
what do you think guys?