RottenBananas Posted July 19, 2008 Share Posted July 19, 2008 Hey guys, Hoping you could help with designing my db. Very new to sql and databases so a bit confused. What I have so far is a users table with everything in it...i dont feel as though keeping everything in one table is a good idea. Heres how my app should work: - Two types of users: normal user, organization - normal user can setup a profile with this info [name,birthday,hometown,occupation,interests, can upload music,vids and pics] - an organization has this info in their profile[point of contact,location,state,phone, can also upload music,vids and pics] - an organization can post events and normal users cannot - a normal user can click a button to confirm them coming to the event - a way to list all events per state - a commenting system where registered users can comment on both types of users pages So im thinking these tables: users, events, profile, videos, pics, music I just dont know how everything would work together. More specifically which fields go under each table so that they are linked correctly Any advice? Thanks Quote Link to comment Share on other sites More sharing options...
Highlander Posted July 28, 2008 Share Posted July 28, 2008 Read about foreign keys Quote Link to comment Share on other sites More sharing options...
JonnoTheDev Posted July 28, 2008 Share Posted July 28, 2008 Read a bit about database normalization. Some hints for you: 1. If a user only has one profile then this should be contained in the user table. 1 to 1 joins are not best practice. 2. If there will be only 2 types of users for the lifetime of the app then this can be an ENUM field in the users table. If more will be added then a userTypes table is best with the typeId a foreign key in the users table. 3. If users are allocated to events then you will need a join table such as usersToEvents where the userId and the eventId are foreign keys to events and users. 4. If users can add many videos, music and pictures then these should be tables using a userId as a foreign key Quote Link to comment Share on other sites More sharing options...
RottenBananas Posted July 28, 2008 Author Share Posted July 28, 2008 Thanks for the help, After reading about foreign keys stuff started making alot more sense Thanks again 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.