I want to store one ipaddress, two macaddresses and one timestamp as one table in a database.

|ip|mac1|mac2|timestamp|

This information can come in any configuration (almost), and nothing in this table functionally depends on anything else.

Does this mean I have to make the primary key as all the attributes together? primary key = ip,mac1,mac2,timestamp

Is there anyting I can do about all the repeated information? For example, I will get many many posts looking exatcly the same, having just the timestamp changed.

Also - I need to be able to search quickly. Do I need to create an index on all the attributes separately?

Finally - I'm going to do extremely many and fast inserts over a very long period of time. I must be able to search and get this information during this time in the same database. This shouldn't be a problem right?

I'm using Mysql 5.0.27 and MyIsam.

why do you want to plug four separate values into a single column?

separate columns makes much more sense

for example, you cannot possibly hope to search efficiently on any single piece of the 4-value munge

Thanks for your reply!

Yes, exactly, this is very denormalized. I was thinking of better ways to do it.
Whould this be a good solution?

From the main table I create this:

|packetid|

And now I create 4 smaller tables:

|packetid|ip|
|packetid|mac1|
|packetid|mac2|
|packetid|time|

where the data is the primary key, and packet id is a foreign key referencing the main-table value. (I can't use MyIsam anymore with foreign keys right?)
And to be able to search quickly, I need to add an index to each of these, but since they are primary keys, I beleive they will all be indexed by default.


What I am afraid of here is speed decrease, I need to do 5 inserts instead of 1. And since the packets are arriving at such a tight interval, I might not have the time. Do you think this will affect the perfomance so much that it wont make up for the normalization?

no, not 5 tables, just one table, with 4 separate columns

Oh, sorry, I didn't get you there, | = column delimiter. Certainly all of them would be stored in different columns. And thus - my questions persist, sorry.

that definitely sounded like you wanted one column, not four

yea, reading it, I totally agree, pardon my terminology, I edited the post, field should be table and table should be database of course.

that's better

Yonder,

A primary key should 'technically' be the candidate key with the smallest number of columns that will uniquely identify a single row.

Without knowing more about the data it's difficult to say. For example, if we're talking computer MAC addresses they are globablly unique anyway so that combined with the time would probably be an acceptable primary key, but personally I don't like including time stamps as part of a key simply because it involves problems when building front-end for stuff. E.g. on a web front end, to delete stuff... I'd need to pass the mac and date.

For this reason, and given that you've only got 4 fields. I would put in an auto ID column and not worry too much. Don't forget to consider your performance in the equation

I used to think that, until someone informed me that some manufacturers allowed you to specify a MAC address... this leads to the comclusion that a MAC address may not be unique.

tom, what would be the purpose of the auto id?

the original question said "nothing in this table functionally depends on anything else"

it's just an easy guaranteed PK. Not knowing anything about the application it's difficult to advise if this would be beneficial or not.

hmm well, lets say we have 100k different ips, 50k different mac1, 50k different mac2, and infinite different timestamps. The packets arrive very often, so we get alot of data with the exact same timestamp (mysql time res. = secs).
The most common data combination that comes is same ip same mac1, same mac2 and different timestamp. 2nd common is different ips, same mac1, same mac2, and different timestamp.

But maybe It's just better to have them all as is for the performance, and put an index on each of them.