I'm trying to learn more about optimizing my tables in my database with the right column type for the job. right now all I basically use is varchar and int fields. Here is my user table and I was wondering if anyone had any tips on how I can make changes to it to optimize it for performance/space issues. thanks all!!!
st_users will hold all of the street team members personal data
id int(6)
username varchar(40)
pass varchar(40)
first varchar(20) first name
last varchar(20) last name
email varchar(40)
bio text brief bio about the user
add1 varchar(60) address 1
add2 varchar(60) address 2
city varchar(40)
state char(2)
joindate int(14) date they joined the site
lastdate int(14) last time they logged in
age varchar(2)
web varchar(60) website address
image1 varchar(40) picture of user
level_id int(2) level of user - contains id from another table
im varchar(40) instant messenger name/service
favsong varchar(30) favorite song
its not a big table but any help would be great to help me in creating larger more complex database schemas
though I do not display the address and I don't collect addresses from every person.. so yea it might be a good idea to split that off to another table
I agree with nielsene, break up your table. A separate address table may come in handy later on too. If you are wanting to add to it's complexity create primary and foreign keys in and between the tables.
What portion of your page views do you think the st_det page is?
Optimizing a DB is almost always a trade off. You'll need to select which tasks you want to be fast. I would split your data up into three tables -- a user/login table, address table, and profile table. Exactly what belong in each is dependant on your application.
The user/login tables should be the only user table a login/access control test should have to check. It should also be the only page that most page views need to check. Address and profile, I would expect, are seldom used which is why I would break them up.
Additionally seperate table gives you more granularity for sql privileges. (Though I think I heard that MySQL can do column level privileges, so that might be a moot point.)
st_users will hold all of the street team members login data
id int(6)
username varchar(40)
pass varchar(40)
first varchar(20) first name
last varchar(20) last name
email varchar(40)
lastdate int(14) last time they logged in
st_profile will hold all of the users personal profile information
user_id int(6)
joindate int(14) date they joined the site
age varchar(2)
web varchar(60) website address
image1 varchar(40) picture of user
level_id int(2) level of user
im varchar(40) instant messenger
favsong varchar(30) favorite song
bio text brief bio about the user
st_address will hold the address of users for payback and sending out promo items
user_id int(6)
add1 varchar(60) address 1
add2 varchar(60) address 2
city varchar(40)
state char(2)
any other tips on column types or structure?
Last edited by JPlush76 on Mon Aug 26, 2002 1:14 pm, edited 3 times in total.
You need a way to reference the proper profile and address information on a user. As it stands, how will you know what street address / profile goes with which user?
You could make the 'id' field in each of the other tables ('st_profile' and 'st_address') and make 'id' a unique field or a primary key.
I would probably make the two satellite tables use "id" that is both a foreign key to st_users and is the primary key for the individual table. Both tables are 1:1 so there is no need for a seperate unique id.
There is not always a right or wrong answer when designing databases, it is more of an art than a science.
As the relationships you are describing between the tables are all 1:1 I would go back to your original design, it doesn't really matter that you will have fields with null in them, these days disk space is not really an issue.
Only thing to do is make sure fields that can be null are the last fields in your table structure.
Whilst there is nothing wrong with having 1:1 relationships it will increase your applications complexity when it is not really required to be so complex.
If you delete a user you will need to delete from 3 tables, and make sure all of the transactions are executed successfully, if not then roll-back.
Same for adding a user.
Increased complexity for updating user details.
Increased complexity for simple queries (in terms of potentially joining 3 tables, and they would need to be LEFT/RIGHT JOINS as only your main table would definitely have a record)
So I will have to disagree with the majority on this one, just have the one table.
I agree with mikeq, it'll be a lot simpler and neater to have everything you described in one table. If you were going to be saving multiple addresses per person then another table would be handy but that's something different.
But in light of the original post splitting the table, adding primary and foreign keys, as well as adding unique indexes would increase JPlush76's understanding of relational databases which is what JPlush76's original post implied he/she (I don't assume gender anymore Mac ) wanted. Granted, it could be made easier but so could most of the assignments in my CPS 541 Relational Databases class.
Part of good database design also has to be keeping things as simple as possible. I can remember setting up something similar to JPlush76 with lots of tables with records all referenced by this one ID value and all with 1:1 relationship to each other. It was a nightmare to administer and really was a very bad example of relational database design. Just because you can do something doesn't always mean you should and you'll value simplicity in the long run... My £0.02 .
I agree that more people should learn correct relational design, but by splitting a table into 3 1:1 relations IMHO is not good relational design. The original 1 table did not break the rules of third normal form.
Its all a matter of design trade-offs. Every DBA I've talked with has advised for the multiple 1:1 table approach discussed here, however. In this case some of the trade offs is simplicity versus security and speed versus speed.
Security because of the easier ability to restrict access to the appropriate level. The speed up bonuses from splitting the table: smaller tables are faster, smaller tables may stay in RAM longer. Queries that need to touch two tables will be slower than before, but what percent of your queries are those?
As a developer I also find smaller tables easier to deal with if I need to manually twiddle bits or dump databases for debugging. If the whole table fits across one screen width its a huge help. Yes I can always restrict which columns I want, but that's more work, why make debugging harder?
Concering some of the counter arguements:
Well the original table probably didn't break any of the rules of normalization (State and City could be breaking it in some cases, likewise favorite song, if its chosen from the songs of the o.p's band.) Splitting a table to multiple 1:1 tables doesn't to my knowledge break it either. Good relational db design is much more than just applying the rules of normalization. Often its knowing when to break them, too.
Yes this system might be slightly more complex, but ideally you would have functional code that wraps it so that you don't worry about it more than once.
Some RDBMS allow you to assign behavoir for "on delete" and "on update" of primary keys referenced by a foreign key. Ie you could make the delete to the core users table cascade to all the satellite user tables. Even in RDBMS that don't support this yet, you can typically accomplish the same with Rules,Triggers,Stored Procedures, etc.
I'll concede the point that some "simple" queires become more complicated with multiple tables, but I suspect that the majority of his queries only touch one of the subtables, the core table; so I think its a weak arguement.
Fundamentally I'll agree with those saying that splitting is probably not needed in this case, but if the original poster wants to learn more about the principle's of database design and administration. This would be a relatively straightforward way.
As one grows in DB understanding a database should become a collection of tables, users, groups, indexes, rules/triggers/stored procedures/etc. Each level of understanding lets the RDBMS do more work for you and your application do less and in almost all cases the RDBMS is faster than your code.