0

I'm building a small forum component for a website where sub-forums have different admins and mods responsible for them, and users can be banned from individual sub-forums. The ban table I have looks like this:

_Bantable_ 
user_id 
group_id
start_date 
end_date 
banned_by 
comment

At first I was going to use the first four columns as the primary key, but now I'm wondering if it would matter if I use one at all, since no-one would be banned at the same exact time from the same forum, and regardless I'd still have to check if they were already banned and during what interval. Should I just not use a key here, and simply create an index on the user_id, and group_id and search through those when needed?

Jaigus
  • 1,422
  • 1
  • 16
  • 31

3 Answers3

1

It wasn't 100% clear, but it sounds like you want temporary ban functionality on a per user basis for a particular groupId. If this is the case, you should make a composite primary key:

user_id,
group_id,
end_date

This will let you do

SELECT * FROM bantable WHERE user_id=$currentUserToCheck AND group_id=$currentGroupToCheck AND end_date < $currentDate

or something like that

Note: if you want your primary key to be coherent in terms of whatever database design principle you're adhering to, then you can just make the primary key the user_id (because it is indeed a unique identifier), and then make a composite index on the three columns that i specified above.

Be absolutely sure that any queries you run against this table that require individual indexes have those indexes correctly generated.

Zoltan Toth
  • 46,981
  • 12
  • 120
  • 134
RedHydra
  • 163
  • 8
  • Thanks for the reply, yeah I could have been more expressive, but I thought the "expires" date was indicative of the ban being temporary. I will use a variation of this that I explain in the other comment. – Jaigus Sep 04 '12 at 04:10
0

Why dont you just take the user_id as primary key? I mean you don't even have to use auto_increment (which obviously would not make any sense in here).

Guessing that you'd request the user_id anyway on login this would probably provide the best performance to look if there is even an entry for banning matters.

Zoltan Toth
  • 46,981
  • 12
  • 120
  • 134
androidavid
  • 1,258
  • 1
  • 11
  • 20
0

Do you need the historical record of past bans?

  • If not, just create a composite PK on {user_id, group_id}. Whatever data is currently in the _Bantable_ determines who is currently banned. When the ban expires, just delete the corresponding row (and consider whether you need the end_date at all1).

  • If you do need the historic record, put an active ban into your original table as before, but when the ban expires, don't just delete it - instead move it into a separate "history" table, which would have a surrogate PK2 independent from {user_id, group_id} (so a same user/group pair can be in multiple rows) and a trigger that prevents time overlaps (something like this).

1 If this is the date at which the ban is going to end, then you do need it. If this is the date the ban has ended, then you don't - the row will be gone by then.

2 Or alternatively, a PK on {user_id, group_id, start_date}.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I decided to create an "infraction" table where a certain amount of infractions (3 by default) will ban a user, and the ban will be temporary (a week by default). The nth infraction that causes the ban will document the history of the ban(perhaps in the comment column), and the ban itself will be removed from the ban table when it expires. But is there a way of deleting the ban from the table automatically? Or should I just create a thread that checks periodically for expired ban records? – Jaigus Sep 04 '12 at 04:08
  • @RoryBreaker Yes you could do it periodically. Or, just check if the ban expired whenever you check if the given user is banned (and just remove it from the table if expired). – Branko Dimitrijevic Sep 04 '12 at 10:02