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 ?

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

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

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

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.