[SQL] Relationeel vraagstuk

[4Real]

Voor een hobby project wil ik een soort dagboek maken, ala hoe Facebook met gebeurtenissen omgaat. Het idee is dat ik een gebeurtenis kan toevoegen en hieraan een locatie kan koppelen, maar een locatie is nog al dubbelzinnig. Zo kun je vandaag naar Amsterdam gaan om te shoppen en een andere dag met vrienden naar een bepaalde bar gaan om wat te gaan drinken. Met deze redenering kom ik al op twee entiteiten uit: (1) stad en (2) bedrijf. Deze twee entiteiten hebben al een overlap, namelijk lengte- en breedtegraad. Dus om redundantie in mijn database model te verminderen heb ik gepoogd om de tabellen zo in te richten dat deze niet dubbel worden opgeslagen.

Hierdoor ben ik tot het volgende model gekomen (niet volledig, maar even om probleem duidelijk te maken):


Als ik nu vanaf een stad en een bedrijf kijk welke gebeurtenissen hieraan zijn gekoppeld dan zijn de queries bouwen niet moeilijk, maar wanneer ik een gebeurtenis bekijk dan weet ik niet hoe ik het onderscheid moet bepalen of deze plaats heeft gevonden in een stad of een bedrijf. Vanaf event is het eenvoudig om bij location uit te komen, maar hier is het eigenlijk een T-splitsing en hier loop ik vast om de programmatuur te laten bepalen welke kant hij op moet gaan.

Door middel van meerdere queries zou ik het wel kunnen realiseren, door te kijken of LocationID voorkomt in de tabel City of Street en vanaf daar dan de rest van de informatie op te halen. Performance technisch gezien zou het mooiste zijn om dit met één query uit te kunnen voeren, maar is dit uberhaupt mogelijk of is mijn model hiervoor niet goed ingericht?

[NMe]

Waarom maak je het jezelf zo moeilijk? Als je dan al bedrijven van plaatsen wil scheiden (geen idee of dat al dan niet nodig is), dan heb je die straat er vervolgens toch niet bij nodig? En zelfs als je die wél nodig hebt, dan laat je die info toch gewoon in Location of Company staan?

IMO heb je die koppeltabel tussen Event en Location niet nodig, want één event zou maar één location moeten hebben. Is dat niet zo, dan is event misschien niet zo'n goede naam. Als je jezelf vervolgens beperkt tot City en Company dan heb je Location niet meer nodig, en Street en CompanyLocation ook niet. In je Event neem je dan gewoon twee velden op: eentje genaamd CityId en eentje genaamd CompanyId. En om het jezelf makkelijk te maken: Company kan ook weer verwijzen naar City.

Jouw ontwerp lijkt vooral gebaseerd te zijn op een drang om vooral maar in 3NV te blijven. Redundantie is niet per definitie slecht, er zijn niet voor niks nog 3 normaalvormen ná de derde vorm.

[ Voor 3% gewijzigd door NMe op 06-08-2013 10:58 ]

[4Real]

NMe schreef op dinsdag 06 augustus 2013 @ 10:56:
Waarom maak je het jezelf zo moeilijk? Als je dan al bedrijven van plaatsen wil scheiden (geen idee of dat al dan niet nodig is), dan heb je die straat er vervolgens toch niet bij nodig? En zelfs als je die wél nodig hebt, dan laat je die info toch gewoon in Location of Company staan?

Ik heb een tijdje nagedacht over bepaalde kolommen leeg te laten, maar kwam na een tijdje op deze oplossing en vond hem mooier. Echter als ik je reactie goed begrijp dan doel je op iets als het volgende:



CityID & CompanyID kunnen NULL zijn. In een extreem geval kan Street en Zipcode ook nog NULL zijn mocht je een feestje hebben in de middel van de Sahara

Maar door CityID en CompanyID kan ik dan door een left join extra informatie ophalen in dezelfde query. Door een simpele null check op CityID kan ik dan de keuze maken om extra informatie te tonen bij een gebeurtenis.

IMO heb je die koppeltabel tussen Event en Location niet nodig, want één event zou maar één location moeten hebben. Is dat niet zo, dan is event misschien niet zo'n goede naam.

Klopt, maar één locatie kan meerdere events hebben. Dit kan dan opgelost worden door een koppel tabel of door hoe ik het in deze post heb opgelost een foreign key op te nemen in de Event tabel.

Als je jezelf vervolgens beperkt tot City en Company dan heb je Location niet meer nodig, en Street en CompanyLocation ook niet. In je Event neem je dan gewoon twee velden op: eentje genaamd CityId en eentje genaamd CompanyId. En om het jezelf makkelijk te maken: Company kan ook weer verwijzen naar City.

Laatste wat je zegt is nog een goede, want in Company zou ik eigenlijk nog een foreign key moeten opnemen naar City. Anders moet ik via Location de City bepalen. Volgens mij is één foreign key opnemen dan makkelijker aangezien ik dan de query eenvoudiger kunt maken.

Jouw ontwerp lijkt vooral gebaseerd te zijn op een drang om vooral maar in 3NV te blijven. Redundantie is niet per definitie slecht, er zijn niet voor niks nog 3 normaalvormen ná de derde vorm.

Ik zal hier eens naar kijken om te zien of dit mij in de toekomst kan helpen.

[Voutloos]

Je doet moeilijk. Een event wordt (optioneel?) georganiseerd door een company, dus relatie. Verder hebben zowel event als company zelf een latlon. Als bedrijf verhuist moet de locatie van events in het verleden niet wijzigen.

En last but not least heb je dus iets als land, plaatsnaam etc. Die zijn af te leiden vanaf latlon, maar als je niet om elke poep of scheet een (reverse) geocode wil doen en/of op dat soort eigenschappen wil kunnen queryen, sla je ze lekker bij event en company zelf op.

Done. Als je adres data wel gaat normaliseren, moet je ook een propere bron erbij nemen, anders kan je nog geen drol als een plaats opgesplitst/samengevoegd etc. etc. wordt.

[4Real]

Nee een event wordt niet georganiseerd door een bedrijf. Het kan iets zijn als ik ga shoppen in Amsterdam. Event description bevat dan de tekst ik ga shoppen en er wordt een relatie gelegd naar de stad Amsterdam. Mocht ik naar Paradiso gaan voor een concert dan wordt er een relatie gemaakt naar het bedrijf Paradiso en naar de stad Amsterdam.

Maar wat je zegt over opslaan van informatie -verhuizen van bedrijven- is wel een goed punt. Nu heb ik hier met steden nog niet echt snel problemen mee, maar een bedrijf kan nog wel een keer verhuizen. Wat ik dan in dit model moet opnemen is een locatie voor het bedrijf. Zo kun je events nog steeds op die locatie opslaan en kan het bedrijf verhuizen ongeacht dat dit voorgaande event aantast.

[drm]

Het heeft geen enkele zin om een aparte entiteit van de lat/lng te maken, aangezien je voor relevante queries toch de GIS hoek in moet duiken en moet gaan berekenen of iets "in de buurt van" iets anders is. Relaties daarvoor gebruiken is absoluut zinloos.

Verder is het opslaan van de lat/lng feitelijk caching van de geocoding. Die zou helemaal niet van invloed moeten zijn op je normalisatie. Het adres (of de plaats) geeft immers al een geolocatie (welliswaar met een bepaalde resolutie) aan. Dat je die dan vervolgens ook in lat/lng uit kunt drukken is irrelevant. Je zou ook RD coordinaten op kunnen nemen, of hoe de straatnaam in 1861 heette. Dat is allemaal irrelevant voor je datamodel, want het adres is gewoon wat het is.

Als je dit in je achterhoofd houdt, wordt het vanzelf veel eenvoudiger. Premature optimization is the root of all evil.

[NMe]

4Real schreef op dinsdag 06 augustus 2013 @ 12:06:
Nee een event wordt niet georganiseerd door een bedrijf. Het kan iets zijn als ik ga shoppen in Amsterdam. Event description bevat dan de tekst ik ga shoppen en er wordt een relatie gelegd naar de stad Amsterdam. Mocht ik naar Paradiso gaan voor een concert dan wordt er een relatie gemaakt naar het bedrijf Paradiso en naar de stad Amsterdam.

Dat verandert verder niet veel aan zijn betoog.

Om terug te komen op je tekening in je reactie op mijn post: dat is min of meer wat ik bedoelde, maar ik zou dan zelfs Location nog weglaten en alles wat je daar hebt ondergebracht aan het event koppelen. Zie wat Voutloos zegt. Wat je feitelijk doet is een stukje denormaliseren door data redundant op te slaan op een manier die handiger is om te queryen. Wat je uiteindelijk wil voorkomen is dat je data een hele mooie vorm heeft maar bij 10.000 records je database al op zijn knieën helpt. Helaas zeg ik dat uit ervaring.

[DEiE]

Kan je de gebruiker niet een lijstje geven met mogelijke locaties in de buurt (steden in de buurt, evenementen, etc.)?

Ik krijg namelijk het idee uit je post dat je ervanuit gaat dat je precies op de juiste locatie bent als je de post aanmaakt. Precies op de locatie van het event of de bar, etc. In de praktijk zal het alleen niet zo uitpakken.

Je hebt altijd te maken met een radius waar je je in bevindt. Als je je locatie ophaalt aan de hand van de zendmasten is die radius veel groter dan gps, maar je hebt hier alsnog mee te maken. Zo kan het dus zijn dat je een locatie met een radius van 500 meter terug krijgt, waar meerdere barretjes binnen vallen. Je kan dan de meest dichtstbijzijnde bar pakken, maar dit hoeft zeker niet de juiste te zijn.

Aan de andere kant, wanneer je wél een precieze locatie hebt, hoeft dit zeker niet te zeggen dat dit ook de locatie van het evenement of de bar is. De locatie van de bar zal waarschijnlijk de voordeur zijn, terwijl je binnen een andere locatie hebt met een radius waar de bar niet invalt. Ook kan het best zijn dat je je verkeerde locaties matched als je bij een evenement bent, maar aan de zijkant van het terrein staat. De bar aan de overkant kan dan dichterbij zijn dan de locatie die bij het evenement is opgenomen (het podium bijvoorbeeld).

Ten slotte geef je aan dat je ook een stad wilt matchen. Wat is de locatie van een stad, het middelste punt? Aan de rand van de stad ben je dus veel eerder geneigd om één van de omliggende dorpen te matchen, in plaats van een grote stad als Amsterdam.


In mijn ogen lijk je je heel erg te focussen om de locaties zo efficiënt mogelijk in de database op te slaan, zonder duplicaten, etc., zonder dat je stil gestaan bent welke complicaties locaties in de praktijk met zich meebrengen.