jaymc Posted September 21, 2007 Share Posted September 21, 2007 Ok, at current, my database structure is not very normalised, so ive decided its time to recuperate In my members table I have the usual stuff you would expect username fullname password ip address last login registered Then I have all there profile data in there aswell gender location sexuality relationship fav food So, ive decided to split that table in half, but my problem is... how do I decide what to take over to my 'profile_info' table gender could go in members table... but it also applies just as much to the profile_info What should I be taking into account when I make these decisions? To add onto this, I have an INFO field, where a member can enter up to 1000 chars about themselves Would this be better off in its own table? If that was the case... when accessing there profile it would require 3 joins to get the required data (members, profile_info, person_INFO), where as at the moment, its just one query as all of the data I need is contained in the members table So, to sum it up, if I'm partioning tables, how do I decide what needs to be where Feedback greatly apreciated! This will be a big project of mine and it must be a success, no time for trial and error with this one as its a large database Quote Link to comment Share on other sites More sharing options...
effigy Posted September 22, 2007 Share Posted September 22, 2007 How is having all this data in the user table not normalized? As long as gender, location, sexuality, relationship, and fav food are actually IDs that point to separate tables, this should be OK. Do all users have profiles? Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 22, 2007 Author Share Posted September 22, 2007 All uses hava a member row, which contains all the information when registering long as gender, location, sexuality, relationship, and fav food are actually IDs that point to separate tables Huh? Why would they need to point to seperate tables, and why ID's? Check the screenshots below, one is the table structure and the other is sample data What I was thinking, data such as email is never used in any of my scripts, apart from mailshots etc which get executed monthly. Is that the sort of data that should be in another table? Also, the last songs field, that is only used when viewing someones profile to see the last songs they listened to [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
effigy Posted September 23, 2007 Share Posted September 23, 2007 I think I see where you're going. Yes, you could make the user table only login-credential specific, and create a profile table that would have a foreign key linking to the user table. Huh? Why would they need to point to seperate tables, and why ID's? How is this input controlled and/or updated? Is it hard-coded into the system? Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 23, 2007 Author Share Posted September 23, 2007 Its all hard coded yes Typical example, for every 3 minutes there logged into the site an AJAX refreshed will fetch a script and update there last action Currently, there last action is in the members table with there login credentials and profile data etc.. The thing I dont like about splitting tables up and using joins, surely its like doing 3 seperate queries? Surely its better to fetch one row of data and get everything you need rather than 3 seperate tables.. Quote Link to comment Share on other sites More sharing options...
effigy Posted September 24, 2007 Share Posted September 24, 2007 Fields like location and favorite food are hard-coded? This isn't very scalable. You've been concerned about joins, and in turn are shy about normalization. I would create a user table that is only login-specific and another table that is profile-specific. When the user logs in, store their ID in a session/cookie and use it to access the profile table and make any needed joins. This gives you only 1 join at minimum. Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 25, 2007 Author Share Posted September 25, 2007 Sorry, location is hard coded as in they have a selection of 50 cities, food can be anything So to sum it up... is it much better to split a table into 2 tables and use joins which will act as 2 queries to get one set of data as aposed to a table with more fields but needing on one query to pull data Ive been reading up on this for the last week and cant seem to find any performance documentation on whether its actually better to use split tables and joins. In theory in my mind it could work both ways.. I just dont want to redesign my database structure and load get worse Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2007 Share Posted September 25, 2007 Normalize it as much as reasonably possible, and then fix it when it breaks. Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 25, 2007 Author Share Posted September 25, 2007 Cheers Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.