table design question

I have "ip_address" and "ports" that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion?

Should I use int for "ports", which will have an index. Not sure how to store "ip_address".

This table has the possibility of having 800 millon records. Thanks

Rob

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

Re: table design question

rmck said:
> I have "ip_address" and "ports" that I want to use in my table. I
> was just going to make each one a varchar. But was wondering if
> anyone has a better suggestion?

PostgreSQL ;-)

It has a native datatype for storing IP addresses. That means that
things like sorting and subnet inclusion tests come included with the
database. But switching databases is not something to be undertaken
lightly.


> Should I use int for "ports", which will have an index. Not sure how
> to store "ip_address".

Is IPv6 an issue?

Jochem





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

Re: table design question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Jochem van Dieten wrote:

> rmck said:
>
>>I have "ip_address" and "ports" that I want to use in my table. I
>>was just going to make each one a varchar. But was wondering if
>>anyone has a better suggestion?
>
>
> PostgreSQL ;-)
>
> It has a native datatype for storing IP addresses. That means that
> things like sorting and subnet inclusion tests come included with the
> database. But switching databases is not something to be undertaken
> lightly.

Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL
manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that
allow you to convert the IP address to an integer....

Regards,

-Mark
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com

Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAGS2MtvXNTca6JD8RAgkdAKC5rcoyIMJFOaklzT8TaLONQLPsqACf UImB
p//aTmYu/i84jWOJ0PqX8Mk=
=OODk
-----END PGP SIGNATURE-----

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

Re: table design question

Mark Matthews wrote:
> Jochem van Dieten wrote:
>> rmck said:
>>>
>>> I have "ip_address" and "ports" that I want to use in my table. I
>>> was just going to make each one a varchar. But was wondering if
>>> anyone has a better suggestion?
>>
>> PostgreSQL ;-)
>>
>> It has a native datatype for storing IP addresses. That means that
>> things like sorting and subnet inclusion tests come included with the
>> database. But switching databases is not something to be undertaken
>> lightly.
>
> Or take a look at the INET_ATON() and INET_NTOA() functions in the MySQL
> manual at http://www.mysql.com/doc/en/Miscellaneous_functions.html that
> allow you to convert the IP address to an integer....

Which is exactly why I asked if IPv6 was an issue.

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje




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

Re: table design question

rmck wrote:
> I have "ip_address" and "ports" that I want to use in my table. I was just going to make each one a varchar. But was wondering if anyone has a better suggestion?
>
> Should I use int for "ports", which will have an index. Not sure how to store "ip_address".
>
Use a varchar for the IP address. It allows greater flexibility and
will adapt to format changes much more easily.
An int for the port number would work well.

--
jimoe at sohnen-moe dot com

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

Re: table design question

> From: Richard Reina
>=20
> I want to create a US geography database. So far I have categories =
such as
> state nick names (some states have more than one), state mottos (text =
25 to
> 150 characters), state name origins (100-300 characters), state =
"trivial
> facts", entry into union. My question is; would it be better to keep =
at
> least some of this information in separate tables...

To me, the key question is cardinality.

You gave a big clue with "some states have more than one." This =
"cardinality rule" clearly indicates you need a separate table for nick =
names.

I'd look carefully at cardinality, and any field in which you can say, =
"some states may have more than one," put it in a separate table.

(One exception to cardinality-driven table design would be if a field is =
a clearly defined, relatively unchanging set of constants. The classic =
example is when different states in a process need to be recorded -- =
"membership" might include the set "applied", "paid", "accepted", =
"withdrawn". You could have multiple states in a SET field, which would =
be much less cumbersome than having a fifth-normal-form join table.)

----------------
A low-energy policy allows for a wide choice of lifestyles and cultures. =
If, on the other hand, a society opts for high energy consumption, its =
social relations must be dictated by technocracy and will be equally =
degrading whether labeled capitalist or socialist. -- Ivan Illich
:::: Jan Steinman, EcoReality Co-op ::::


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