Chat script + mysql

coldfingertips has asked for the wisdom of the Perl Monks concerning the following question:

I need advice on how to do the mysql portion of a chatterbox script. It's similar to the one here but it's an anonymous script (the user types in their username and password each time).

Can someone tell me how THEY would setup the mysql part of this? Like what tables, columns, how you'd do it?

Details:

Admin Panel >
   *Admin usernames and passwords needed for login
   *Reserved name list (people can't use the names like "admin" in the
+ script
   *Swear words the admins wish to check for in a list
   *Blocked IP addresses for innappropriate users (will be checked eac
+h time the script is loaded to see if they can use the chat script)
Chat >
   *Stores their username, IP, date/time of message, message
   *Will print the last 5 or 10 messages stored
Log >
   *Will display all messages in newest>oldest order with 50 messages 
+per page
[download]

I know enough how to do the usernames and passwords but I'm not sure if I should make a column for blocked IPs and each IP has their own row (same thing with the swear words to watch for and reserved names).

I was thinking it would be three tables. ADMIN (login information), CHAT (messages, ip, username, etc) NITTYGRITTY (blocked IPs, swear words, reserved usernames that users can't use, etc).

If it makes any difference, when the admin logs in it'll store their information in a COOKIE which the script checks. If this cookie exists they can use the RESERVED NAMES. No one else can use the names.

Before I go out and about and do this, if any of you has a better form to create this please let me know.

Comment on Chat script + mysql Download Code

Replies are listed 'Best First'.
Re: Chat script + mysql by eric256 (Parson) on Feb 17, 2005 at 18:19 UTC
One table for each "set" of information. So you have a set of banned IP's, thats a table, a set of swear words (a table), and a set of reserved names. A set of login credintials and a set of messages. Looks like 5 tables to me. Update: I looks like your reserved names are actualy the set of users with passwords. So maybe those two sets are actualy one, if you want to use a reserved name you have to know the password that goes with it. I thought maybe you had a set of reserved but not used names. Even in that case they could simply be logins that no one knows the password to. ;) ___________ Eric Hodges	[reply]
Re^2: Chat script + mysql by coldfingertips (Pilgrim) on Feb 17, 2005 at 18:25 UTC
I should better explain that. The admin logs in with their username or password to administer the chat (delete bad messages, ban users, add swear words to the filter). Any admin as long as they have their cookie (user:pass authenticated) can use any RESERVED name they want. So the admin logs in, gets a cookie and gets to use the reserved names if they want along with other administrative duties.	[reply]
Re^3: Chat script + mysql by eric256 (Parson) on Feb 17, 2005 at 18:34 UTC
Looks like I misunderstood twice. Either way you should be able to better figure out which tables you need. In general you should have a table for each distinct set of data you have. Sometimes figureing out what constitutes a set can be difficult. In those cases think about the information and what will make it most accessible. You can cram data into all sorts of contorted forms so look for one that will be easy to work with now AND expand latter. Best of luck. ___________ Eric Hodges	[reply]