mysql中的多维表

Question

Is it possible to have a multi-dimensional table in mySQL

I want to log users based on based on their ip, their user-agent and the session token they sent.

Something like this:

ip1
    user-agent1
        session token1
    user-agent2
        session token2
        session token3
ip2
    user-agent3
        session token4

I would like to avoid using serialize() as I've heard it would make the table less efficient and less portable.

Answer 1

It isn't that you have a multi-dimensional table, but rather multiple tables.

Table ips

id INT
ip VARCHAR

Table useragents (links UA to IP)

ip_id INT
useragent VARCHAR(2048) (some large value)

Table tokens (links users to tokens)

id
userid 
token

All three of the components above are linked together via their id columns into a table identifying sessions. Multiple ip_id may associate with a single token, which in turn associates multiple possible UA strings.

It leaves the possibility that there can be identical UA strings for the same IP, but with multiple different session tokens from that same IP.

Table sessions

ip_id
token_id