IP Address lookup star schema design I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets and now the next steps is to populate this datasets in to the table. And then have some service setup that will accept IP Address as an input and then lookup the database basis on IP Address. So below is the dataset will look like-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us


Problem Statement:-

We are expecting traffic around 100 Millions calls per day for the IP Address lookup.

And this dataset will be worldwide datasets meaning for all the countries. And we are planning to store these datasets in MySql database which is In Memory Database here.

Now my question is should I create only a single table with the above mentioned columns? Or should I normalize the above table into two or three? And do I need to create an index on any columns if any.?

Basically I am trying to know how should I setup the schema for this table so that lookup doesn't take that much time with the traffic we are going to expect. And our service which will be doing lookups should return the response very fast.

I was going through the star schema so If I need to go forward with Star Schema then how can I do that?
Assuming you are using SQL Server, you could adapt this to work:

CREATE TABLE [dbo].[IP2Location](
    [ID] [int] NOT NULL CONSTRAINT PK_IP2Location PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
    [ip] [bigint] NULL,
    [country_code] [varchar](2) NULL,
    [country_name] [varchar](255) NULL,
    [region_name] [varchar](255) NULL,
    [city_name] [varchar](255) NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [zip_code] [varchar](255) NULL,
    [time_zone] [varchar](255) NULL,
    [isp] [varchar](255) NULL,
    [domain] [varchar](255) NULL,
    [net_speed] [varchar](255) NULL,
    [idd_code] [varchar](255) NULL,
    [area_code] [varchar](255) NULL,
    [IP2LocationTimeStamp] [timestamp] NOT NULL,
);

/*
    Create a non-clustered index on the numeric IP address to speed lookups
    I indexed the country_code field here as well since our lookups use it.
    I INCLUDED country_name, region_name, and time_zone since that is what we 
    typically request in a SELECT statement from this table.
*/
CREATE NONCLUSTERED INDEX [IX_IP2Location_IP2LocationServiceQuery] ON [dbo].[IP2Location]
(
    [ip] ASC,
    [country_code] ASC
)
INCLUDE (   [country_name],
    [region_name],
    [time_zone])
GO

/* 
    Create BIGINT versions of the dotted-ip field ip_address
*/
UPDATE IP2Location SET IP = 
            (CAST(substring(ip_address,1,charindex('.',ip_address)-1) AS BIGINT) * 0x01000000) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address))+1, (charindex('.', ip_address, charindex('.', ip_address, charindex('.',ip_address))+1)-charindex('.',ip_address,charindex('.',ip_address)))-1) AS BIGINT) * 0x00010000) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1, charindex('.', ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1)) as BIGINT) * 0x00000100) 
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1)+1, len(ip_address) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1))) as BIGINT)
            );

/*
    Function to return a TVF containing location info about the given numeric IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone 
        FROM GetLocationFromIPv4(16777215);
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4]
(
    @IP bigint
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    INSERT INTO @IPLoc
    SELECT country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code 
    FROM IP2Location
    WHERE ip = @IP;
    RETURN
END
GO

/*
    Function to return a TVF containing location info about the given dotted-decimal IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone 
        FROM GetLocationFromIPv4('192.168.0.1');
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4String]
(
    @IP nvarchar(15)
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    DECLARE @IPInt64 bigint;
    DECLARE @n1 bigint;
    DECLARE @n2 bigint;
    DECLARE @n3 bigint;
    DECLARE @n4 bigint;

    set @n1 = substring(@IP
        ,1
        ,charindex('.',@IP)-1
        );

    set @n2 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP))+1
            , (charindex('.', @IP, charindex('.', @IP, charindex('.',@IP))+1)-charindex('.',@IP,charindex('.',@IP)))-1
        );

    set @n3 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1
            , charindex('.', @IP, charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1)
        );

    set @n4 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1)+1
            , len(@IP) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1))
        );

    SET @IPInt64 = (@n1 * 0x01000000) + (@n2 * 0x00010000) + (@n3 * 0x00000100) + @n4;

    INSERT INTO @IPLoc
    SELECT TOP(1)country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code 
    FROM IP2Location
    WHERE ip = @IPInt64;
    RETURN
END
GO