Database normalisatie. NAW gegegevens.

[iH8]

Ik ben bezig met een bestaand CMS van me te internationaliseren. Ik heb vrijwel alles keurig op orde maar ik struikel nog over iets. Het opslaan van de NAW gegevens van de gebruiker. Hier in Nederland hebben we een postcode met vier cijfers en twee letters. Dat valideer ik zo en sla ik op als VARCHAR6. Nu ben ik er dus Duitsland bij aan het bakken en die blijken uit vijf cijfers te bestaan of meer misschien zelfs. Er van uitgaande dat er over elk bestaand land wel dergelijke verschillen zijn tussen alle gegevens kwam ik in mijn hoofd al uit op per land een verschillende tabel te maken. So far so good

Nu heb ik ook heb ik ook met orders te maken. Een gebruiker kan één faktuuradres bezitten en meerdere afleveradressen. Ik bedacht me het dus ook voor zou kunnen komen dat iemand met een duits faktuuradres iets naar nederland wil sturen of anderom natuurlijk. Nu heb ik dus zoiets bedacht:

user.id
user.name

location.user_id
location.country_id

country_id.user_id
country_id.street
country_id.po

Zo kan iemand meerdere adressen in meerdere landen hebben. Nu weet ik alleen niet of dit zo een normale manier van aanpak is. Door trial en error en veel lezen word ik meestal wijs maar dit wordt me iets te complex om zo maar even aan te pakken en ik kan er maar bar weinig over vinden. Ik werk met SQL (MySQL) maar ik denk dat dat voor mijn denkprobleem niet relevant is. Is het uberhaubt gebruikelijk om vanuit een column te verwijzen naar een tabel?

Kortom, ben ik een beetje goed op weg of gruwelijk fout?

[RobIII]

iH8 schreef op maandag 13 oktober 2008 @ 21:29:
Er van uitgaande dat er over elk bestaand land wel dergelijke verschillen zijn tussen alle gegevens kwam ik in mijn hoofd al uit op per land een verschillende tabel te maken. So far so good

Nope. Bad Idea^TM.

Je zorgt gewoon dat je applicatie de postcode valideert en mikkert gewoon alles in een enkele tabel met adressen met (bijv) varchar(10) ofzo voor de postcode. Je zult merken dat, behalve verschillen in postcode, er nog meer verschillen zijn zoals bijvoorbeeld dat engelstaligen vaak het concept 'tussenvoegsel' (bijv. "van der") niet kennen of dat je bijv. in de US ook een state opgeeft die we hier weer niet doen (enigszins vergelijkbaar met het opgeven van een provincie) etc. etc. Als je zo met alle uitzonderingen rekening gaat houden heb je straks een monster van een DB. Postcodes alleen al zijn er in -tig varianten; dat zou -tig tabellen betekenen.

Als je het normaliseren een beetje beheert had je dat ook geweten; ik denk dus dat je je daar nog even in moet verdiepen

[ Voor 65% gewijzigd door RobIII op 13-10-2008 22:15 ]

[Johnny]

De volgorde waarop de verschillende onderdelen van het adres worden weergegeven per land verschilt ook.

Daarnaast kunnen postadressen in Nederland nog een postbusnummer, antwoordnummer en een regel "Ter attentie van ..." hebben.

Hoe je dat het beste kan normaliseren is afhankelijk van je applicatie en hoe je de gegevens gaat gebruiken. Soms is het handig om gewoon een tekstveld te hebben waar het volledige adres in kan worden ingevoerd, wat natuurlijk flexibel is als je ook internationale adressen wil gebruiken, maar dat is natuurlijk niet makkelijk automatisch te valideren. Je kan ook een boel kolommen toevoegen die optioneel zijn en dan afhankelijk van het gekozen land de velden die van toepassing zijn voor dat land in de juiste volgorde tonen.

[ Voor 13% gewijzigd door Johnny op 13-10-2008 22:31 ]

[w!mz]

Je kan zoals hierboven staat opteren voor meerdere velden in je DB; en afhankelijk van het gekozen land bepaalde velden laten vullen door je applicatie. Dan moet je alleen wel op landbasis ergens gaan vastleggen welke velden uit je NAW tabel getoond moeten worden en op welke plekken. Bijkomend probleem is dan je validatie. In het ergste geval krijgt elk land zijn eigen veldenset in je NAW tabel en heb je praktisch gezien nog per land een verschillende tabel alleen dan in 1 tabel ..

Wat misschien makkelijker is; is om je validatie land afhankelijk te maken en je velden generieke namen te geven (naw.adresLine1, naw.adresLine2, .., naw.adresLineN). In je code krijg je dan in een soort if/else/switch constructie iets van

PHP:

$landGekozen = "defaultLand";
if(isset($_POST['landGekozen ']))
    $landGekozen = $_POST['landGekozen ']

echo "<form action=self.php>";
echo $vertalingAdresR1['$landGekozen'] . "<input name=adresRegel1>";
echo "</form>";

if($landGekozen == "USA")
    // doe allerlei validatie op de velden adresLine1, adresLine2, etc 
elseif($landGekozen == "NL")
    // doe net wat andere validatie op de velden adresLine1, adresLine2, etc 
else
{
    // geen validatie mogelijk; land blijkbaar nog onbekend in code; 
    // waarschuw een sales persoon en bekijk in overleg met
    // ontwikkelaar of land bijgebouwd dient te worden in de code.
}

_{Code is heel kort door de bocht; maar geeft het idee denk ik wel weer.}

Een ontwikkelaar kan zo op landbasis achteraf validaties bijbouwen; ook kan een ontwikkelaar op landbasis aangeven wat de toelichting in het NAW form moet zijn op landbasis.

In het kort; op landbasis NAW formulier tonen, vullen en valideren. Ik denk dat dat je het meeste flexibiliteit geeft omdat je alleen je code maar hoeft te onderhouden op landbasis. Je database kan je ongemoeid laten.

[ Voor 9% gewijzigd door w!mz op 14-10-2008 00:24 ]

[BCC]

Dit is wel heel kort door de bocht inderdaad. Iets meer MVC zou fijn zijn

[Anoniem: 34929]

Wat eventueel ook een overweging is om mensen gewoon het postadres (straat, nummer, postcode, gewest, provincie, staat en land) in één enkel veld te laten typen.

Een one size fits all formule. Probleem is dan je validatie, maar het biedt je wel flexibiliteit om ook b.v. adressen met een andere tekenset te accepteren. (Hoe valideer je een postcode uit Qatar).

Tenzij je de gegevens echt ergens nodig voor hebt (b.v. een apart e-mail veld omdat je applicatie moet kunnen e-mailen), is er in den beginne niets mis met een dergelijke aanpak.

Overigens is dit wel iets wat je het liefst aan het begin van een "levencycle" van je applicatie / db wilt bepalen... nadien van 1 veld naar meerdere velden migreren lijkt me niet fijn...

[Anoniem: 146814]

Afhankelijk van de gebruikte database engine, zal je ook gebruik kunnen maken van triggers om gegevens te valideren. In meta-code:

code:

CREATE TABLE zippatterns(country,pattern);
INSERT INTO zippatterns('nl','^[0-9]{4,4}[A-Z]{2,2}$');

CREATE FUNCTION validate_zip($zip,$country) 
DECLARE
pattern text;
BEGIN
select into pattern pattern from zippatterns where country=$2;
return $1 ~* pattern;
END;

Weet niet zeker of mysql dit kan, postgresql kan 't iig wel.

[RobIII]

Anoniem: 146814 schreef op dinsdag 14 oktober 2008 @ 10:13:
Afhankelijk van de gebruikte database engine, zal je ook gebruik kunnen maken van triggers om gegevens te valideren. In meta-code:
Weet niet zeker of mysql dit kan, postgresql kan 't iig wel.

Dat zijn, IMHO, dingen die je niet door je DBMS wil laten uitvoeren maar door je applicatie. Dat een DBMS het kan wil niet zeggen dat je het ook moet doen Business Logic hoort niet in je DB maar in je applicatie.

[iH8]

Ok ik ben nog een eind van huis lees ik. Ik zal eens wat nieuws uit proberen te werken vanmiddag.

[Woy]

RobIII schreef op maandag 13 oktober 2008 @ 21:47:
[...]
het concept 'tussenvoegsel' (bijv. "van der") niet kennen

offtopic:
Daar liep ik laatst tegen aan, ik ben nog steeds op zoek naar een goede vertaling van het begrip 'tussenvoegsel' in het engels. Ik heb het veld nu maar MidleName genoemd maar dat is natuurlijk niet echt een juiste vertaling

[Face_-_LeSS]

rwb schreef op dinsdag 14 oktober 2008 @ 15:50:
[...]

offtopic:
Ik heb het veld nu maar MidleName genoemd maar dat is natuurlijk niet echt een juiste vertaling

Klopt, want dan zou de juiste vertaling "MiddleName" zijn

Ik zie soms "LastnamePrefix" voorbij komen. Dekt wel redelijk de lading vind ik.

[RobIII]

rwb schreef op dinsdag 14 oktober 2008 @ 15:50:
[...]

offtopic:
Daar liep ik laatst tegen aan, ik ben nog steeds op zoek naar een goede vertaling van het begrip 'tussenvoegsel' in het engels. Ik heb het veld nu maar MidleName genoemd maar dat is natuurlijk niet echt een juiste vertaling

AFAIK bestaat er niet eens een juiste vertaling, omdat ze het niet kennen Middlename is niet zo'n heel slechte vertaling hoewel die vaak ook verwijst naar Rob <de knapste kop van de wereld> Janssen.

Face_-_LeSS schreef op dinsdag 14 oktober 2008 @ 15:56:
Ik zie soms "LastnamePrefix" voorbij komen. Dekt wel redelijk de lading vind ik.

Ik vind prefix anders meer klinken als "tbl_Janssen" Ik had een tijd geleden een mooie vertaling die beter was dan middlename, maar ik weet niet meer waar ik die heb gevonden en het project (DB) waar die in zat kan ik niet (meer) bij

[ Voor 25% gewijzigd door RobIII op 14-10-2008 15:58 ]

[iH8]

offtopic:
Wikipedia: Middle name, ik kan nergens andere benoeming vinden

[ Voor 19% gewijzigd door iH8 op 14-10-2008 16:47 ]

[NetForce1]

iH8 schreef op dinsdag 14 oktober 2008 @ 16:46:

offtopic:
Wikipedia: Middle name, ik kan nergens andere benoeming vinden

offtopic:
gewoon tussenvoegsel noemen? Wikipedia: Tussenvoegsel