Any recommendations as to Database format?

[Mark H]

Hi all,I'm setting up my database and I am wondering if anyone has top tips for a suitable Table structure?I'm doing a minor forum, and have decided upon a Users table and a Topics table, with various columns to keep track of poster, date, topics selected etc.I am thinking that in the "Users" table I would list the topics subscribed to in one column, saved as an array. Similarly, the "Topics" table would list the users that have subscribed to that topic, as an array. I know that this will double information, but it will allow faster checking of the fields that I need to access.(I want to be able to list the "Total subscribers to this topic", and also list "Topics user is subscribed to")Any feedback appreciated.Mark.

[thescientist]

I would just make a separate subscriptions table that associates a user with a topic. I'm not sure how you planned on saving an array to a field...

[Mark H]

Thanks Scientist - yes, that was one issue I didn't know about, whether an array could be saved to a field.Thanks, I'll get down with my pen and paper again and consider this. But off-hand, if I had a separate table to associate users with topics would that do the two things I desire? Quote: - (I want to be able to list the "Total subscribers to this topic", and also list "Topics user is subscribed to").Would the best solution be two extra tables - one to associate a user to various topics, one to associate topics to various users?Thanks for your help, my knowledge of using databases is very poor!

[thescientist]

Thanks Scientist - yes, that was one issue I didn't know about, whether an array could be saved to a field.Thanks, I'll get down with my pen and paper again and consider this. But off-hand, if I had a separate table to associate users with topics would that do the two things I desire? Quote: - (I want to be able to list the "Total subscribers to this topic", and also list "Topics user is subscribed to").Would the best solution be two extra tables - one to associate a user to various topics, one to associate topics to various users?Thanks for your help, my knowledge of using databases is very poor!

that's the same thing. basically its all in how you ask the query. Since a user can only be subscribed to a topic once at a time (you can't be subscribed twice), but a topic can have multiple subscribers, then it's just a matter of constructing the query.

Table: Subscriptions| id |  user_id | topic_id |-----------------------------| 0  |	01	 |  1000	|| 1  |	02	 |  1001	|| 2  |	03	 |  1003	|| 3  |	04	 |  1004	|| 4  |	01	 |  1001	|

Total subscribers to this topicpsuedo-code

select all user_id from subscriptions where topic_id = $id

if topic id = 1001, then you would would get two users returned, users 02, and user 01topics user is subscribed topsuedo-code

select all topic_id from subscriptions where user_id = $id

if user id is equal to 01, then you get two topics returned, topics 1000 and 1001in both cases, the returned amount of records is the count.

[Mark H]

Brill!Thanks a lot scientist..that makes perfect sense now it's explained!Mark.