Getting more involved with database design, help? :)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Getting more involved with database design, help? :)

Post by JPlush76 »

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!!!

Code: Select 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 :D
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

How often do you use all those fields?

I'ld probably split the address section (add1,2,city,state) off to a seperate table still indexed by the same id, user_addresses.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

well I have a details page which would display all of that data about the user

http://www.drawingdown.com/st_det.php?id=19

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 :wink:
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

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.

Direwolf
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Here are a few more comments...

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.)
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

ok cool I broke the tables out like this..

Code: Select all

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.
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

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.

Direwolf
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post by JPlush76 »

yep, I'm a moron... I forgot to add those 8O

I made the edit to my post. Should I create a unique id for those tables anyway?

What benefits come from doing that?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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.

Mac
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

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.


Direwolf
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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 :) .

Mac
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

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.
Post Reply