Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: database design, just one table?

    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.
    Last edited by Yonder; 01-23-07 at 14:55.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not 5 tables, just one table, with 4 separate columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Posts
    6
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Yonder
    I want to store one ipaddress, two macaddresses and one timestamp as one field in a table.
    that definitely sounded like you wanted one column, not four
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Posts
    6
    yea, reading it, I totally agree, pardon my terminology, I edited the post, field should be table and table should be database of course.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2006
    Location
    Bristol, England
    Posts
    7
    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

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by tholder
    ....if we're talking computer MAC addresses they are globablly unique....
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tom, what would be the purpose of the auto id?

    the original question said "nothing in this table functionally depends on anything else"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2006
    Location
    Bristol, England
    Posts
    7
    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.

  13. #13
    Join Date
    Jan 2007
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •