Design question.

I maintain a little open source project that deals with IDS alert
data. I want to add IP reputation to my event queries and I am stuck
on how I should implement it.

The user will have the option of bringing in lists from different
providers and the limit will not be fixed. These lists will be a
single column of IP addresses.

list 1: IP listing
list 2: IP listing
list 3: IP listing
....

There can, and most likely will be duplication of addresses across the
different lists. The number of lists that a host is a member of will
be an indication of its reputation.

The desired result will be something like:

event count | event signature | src ip | country | ip reputation | dst
ip | country | ip reputation

The lists will be updated once each day or on demand.

I already have a mappings table that provides country information for
ip's in the event table which is joined during the event queries. The
mappings table contains a little under 500,000 addresses and grows
slowly - say 50 to 100 addresses / day. As new ip's appear in the
event table, they are mapped to a country.

Questions:

1) Should I just create a new table for every list the user adds and
then do joins on these?
2) Should I put the lists in 1 table somehow?
3) As the lists are done daily, should I just run a midnight task that
parses each list and adds the information to the mappings table. I
have no idea what the format would look like. I was thinking of
creating a varchar and have something like: list1|list2|list9|list20
and then just breaking it out in the code. The entire table would of
course need to be scanned each day to check whether or not an address
had been taken off a list. (efficiency?)

Any comments/suggestions would be greatly appreciated.

Thanks.
--
Paul Halliday
http://www.pintumbler.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org