homepage Welcome to WebmasterWorld Guest from 54.226.80.196
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
SQL tables optimization?
jimmyslam




msg:4082043
 11:40 am on Feb 17, 2010 (gmt 0)

Hello people.

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?

Thanks !

JS

 

LifeinAsia




msg:4082320
 4:24 pm on Feb 17, 2010 (gmt 0)

Shall I put all of that data in the same table?
I wouldn't. In the future, it may be possible that a user may have more than 1 hours or that users could swap houses. It's easier to handle with 2 tables.
Shall I leave it as I have it?
I would, for the above reason.
Shall I use numeric IDs on the houses table and use that ID to link both tables?
I would- numeric searches are always faster than string searches.
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved