help with designing my database

[RottenBananas]

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

[Highlander]

Read about foreign keys

[JonnoTheDev]

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

[RottenBananas]

Thanks for the help, After reading about foreign keys stuff started making alot more sense

 

Thanks again