Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Question Unanswered: Database model for POI

    i have a table of POI. there are around 30 million rows with 40 columns split in 3 or 4 categories (location, opening hours...)

    one key variable is if the POI is a favorite one or not. 5% of these POI are favorite ones.

    90% of our requests are regarding these 5% favorite POI and the remaining 10% requests are regarding the 0.95 x 30 000 000 other rows.

    I have no experience in database modeling as i am a designer. My question is should i split this table in :

    1) one table for favorite POI and one for not favorite POI

    2) have another split per category so i get one table for favorite POI/location, one for favorite POI/opening hours... one for not favorite/location...

    3) or have one table with POI_id + an index on Favorite and put all other information in 3 or 4 tables but not splitting by favorite or not favorite

    I would appreciate if you guys have some guidelines ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in short no
    whether an item is a 'favourite' or not is merely an attribute of that piece of data. not convinced?
    what happens if a POI drops out of favour? do you transfer it to another table or merely remove a flag that indicates its a favourite (or vice versa).
    the potential problem that you may have is what describes a POI
    ferinstance a specific shop / commerical destiantion may well have opening hours, whereas a natural feature may or may not have opening hours. the potential way round this is to subtype the type of POI. the subtyping means you have sub tables that record the specific things that make a type of POI unique
    ie have a separate sub table for commercial POI's eg opemning hours, type of premises, have a separate sub table for, say landscapes / geographical locations. retain the parent tabel for all thats common to all the POI's (say the lat/long, description, date and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    thanks for ur quick reply healdem

    I agree that favorite is just an attribute.

    I should add that all POI are the same type so no need to differentiate them, they are all unique.

    So if i understand you advise me to keep one table of 30 million record with just POI_id and is_favorite + other key variables and create other tables with a primary key = POI_id and containing sub data such as opening hours or location parameters ?

    I don't have a tech background so pardon me if i am a bit slow...

    tks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it depends all on your data and what you want to do with it.
    whether you need subtables or not (look up subtype) is down to you and your business requirements. if you have a lot of similar items, but there are gradations within then sometimes it makes sense to sub type those items, record whats common to all items in one table and then sub type into other tables whats common to each type

    my suspiscion is that you don't need to sub type a single generic table will work

    if you went down the subtype model then you'd have soemthing sort of similar to:-

    table POIs
    ID
    Lat
    Long
    description
    POIType

    table CommercialPOIs
    POIID
    TypeOfCommercialPOI
    opening Hours
    Notes
    ....

    Arguably you wouldn't need a geographical subtype table as it exists


    so my guess is one table to contain all data is sufficient
    on the face of it the PK could be its geographcal position (Lat / Long) but I'd be wary of using decimal values in a PK, especially something such as Lat / Long which would vary with formatting and users precision, but also the source may be suspect, there is alwasy some variation in GPS positions, sometimes it may not be noticable, sometimes it may be in excess of 100m
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Indeed there is a gradation especially for location (GPS lat/long, address, extended address...),

    I will work more on that and test it. I will add some info once i am done so anyone looking for the same topic may find some tips.

    Regarding the PK, lat/long are not unique keys, especially if you use reverse geocoding for address. Some businesses may have the same address. For now i generate an ID, i don't know yet if it is the right choice.

Tags for this Thread

Posting Permissions

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