how should i lay my database tables out? for this scenao

[yobo]

i am hoping to make a site where people make mods for phpbb and upload them to the database and i plan on having seperate tables for each hack catagory for example everything to with admin hacks will be in a table called admin hacks and everything to do with user hacks will be in a table called user_hacks etc..

 

will this be suitable for this situation?

[monk.e.boy]

all hacks in a table called hacks. Then a second table called "type", hacks would have a foreign key into type.

 

Hacks would look like this:

 

id,description,author,fk_type

 

Types would look like this:

 

id,description

 

Then use a JOIN in your SELECT to get:

 

SELECT hacks.description AS h_des, types.description AS t_des

FROM hacks

JOIN types ON types.id = hacks.fk_type

 

Then you will have two returned columns, the hack description and the hack type description.

 

monk.e.boy

 

[monk.e.boy]

Lets assume that the types table also has a 'type' coulmn.

 

So to get all user hacks:

 

SELECT hacks.description AS h_des, types.description AS t_des

FROM hacks

JOIN types ON types.id = hacks.fk_type

WHERE types.type = 1

 

Get all admin hacks:

 

SELECT hacks.description AS h_des, types.description AS t_des

FROM hacks

JOIN types ON types.id = hacks.fk_type

WHERE types.type = 2

 

Get all admin and user hacks

 

SELECT hacks.description AS h_des, types.description AS t_des

FROM hacks

JOIN types ON types.id = hacks.fk_type

WHERE types.type = 2 OR types.type=1

 

monk.e.boy

 

[yobo]

ok and for this scenerio how would i make the submit script becuase i can make a script that submits the hacks but how would i make the script to allow the user to select a catergory that they wish to submit to? and for what ever catagory that they choose it would then be inserted into the correct field within the database?

[monk.e.boy]

Wow. Shall I just teach you how to code  ;) ;)

 

Fill a drop down with the contents of the types.description, the user then chooses the type from the drop down and submits the form.

 

SELECT * FROM types

 

for each $row:

<option value ="$row['id']">$row['description']</option>

 

You then take the value from the drop down (it should be the id of the type row) and all the other data and INSERT it into the hacks table. Put the type id into the fk_type column.

 

So you end up with:

 

Hacks:

1,'my super hack',2

 

Types:

1,'admin'

2,'user'

 

so 'my super hack' is of type 'user'.

 

The previous select statements will dig this data out of the DB for you fine.

 

monk.e.boy

[yobo]

thanks dude for you help. ok also i would like to show the hacks that a user uploaded within the profile page, am i on the right track in thinking this is how i would do it if i create a foreign key in the hacks table called users_id  and then have a table called users and for the primary key of that table use 'id' and when i make the submit form i have a hidden field which holds the current logged in user id so when i submit it so the data goes into the hacks table it should insert the user id into the users_id coloum?

[monk.e.boy]

You got it dude.

 

You is now DB expert and should charge $49.99 an hour     

 

Good work!!!

 

monk.e.boy