Shop Database Layout Overpeinzingen

[Verwijderd]

Ik wil een eigen webwinkel gaan maken. Daarvoor ben ik nu goed over een database structuur aan het denken. Een goed begin is het halve werk. Ik ben een eind op weg, maar ik weet dat hier mensen rondhangen met veel meer ervaring en expertise daarin als ik. Ik heb al rondgezocht op internet, maar er is weinig over te vinden. Ik zou het leuk vinden als jullie meedenken over hoe zo'n ideale structuur eruit moet zien, en het is handig voor mensen die dit ook nog ooit eens van plan zijn. Dus, alle opmerkingen visies benaderingen meningen en vooruitzienende problemen zijn welkom.

De features die ik er nu in verwerkt heb:
- Users met meerdere adressen
- Producten met variaties en categoriën
- Orders met tax, shipping en kortingen

Bijgevoegd een relatieschets zoals ik het nu voor me zie. (Jaja ouderwets met pen en papier!)

[Flard]

Ziet er op zich volgens mij wel goed uit...

Alleen:
- Kunnen producten in verschillende categoriën? (Zo nee, dan moet je Category_id in de tabel products)
- Waarom Users en Adress twee losse tabellen er is toch altijd een 1:1-relatie?
- Ik snap het Options/Variations-principe niet...

[Verwijderd]

Flard schreef op 11 april 2004 @ 14:12:
Ziet er op zich volgens mij wel goed uit...

Alleen:
- Kunnen producten in verschillende categoriën? (Zo nee, dan moet je Category_id in de tabel products)
- Waarom Users en Adress twee losse tabellen er is toch altijd een 1:1-relatie?
- Ik snap het Options/Variations-principe niet...

- Ja dat kan door CAT_MAP. Die kan meerdere producten aan meerdere categorieën linken.
- Omdat sommige gebruikers een ander afleveradres willen hebben
- Stel je voor je wilt een tshirt, maar je kan kiezen rood, blauw, wit en medium, large, extralarge. Dan maak je 2 variations aan: KLEUR en MAAT. Per variatie voer je dan weer de drie opties in met een naam en code.

[chris]

Maar kan het dan, om op t-shirts terug te komen, niet zo zijn dat een girly net 5 euro minder kost? Dus dan zou je price in variations moeten zetten...

[Verwijderd]

chris schreef op 11 april 2004 @ 16:55:
Maar kan het dan, om op t-shirts terug te komen, niet zo zijn dat een girly net 5 euro minder kost? Dus dan zou je price in variations moeten zetten...

You are damn right!

[Verwijderd]

Maar het is dan beter toch price in products te houden en een price_difference veld aan te maken bij options, anders kom je in de knoop wanneer je 2 opties bij een product hebt die invloed hebben op de prijs.

Voorbeeld:

TSHIRT: 20
-Optie 'MAAT'=XL -> prijs + 2
-Optie 'KEUR'=GEEL -> prijs + 8

Totale Prijs = 20 + 2 + 8 = 30

[chris]

Dat is inderdaad helemaal mooi. Heb je er ook al iets ingebouwd voor kortingen? Bijvoorbeeld een korting die de hele maand april geldig is? Je wil volgens mij niet een updatetooltje schrijven dat alle prijzen aanpast, maar juist die over de prijs heen nog een aparte korting doorrekent. En soms kunnen kortingen zich ook stapelen, bijvoorbeeld een korting op een product en ook nog de 10% korting die een klant altijd al heeft.

Verder heb ik nog zelf ervaring met bedrijven die onder 2 namen precies hetzelfde assortiment verkochten, maar dan met een andere winstmarge. Over winstmarge gesproken, die wil je misschien ook wel kunnen instellen. Bijvoorbeeld dat de klant altijd de inkoopsprijs invult, maar dat je zelf vervolgens ervoor zorgt dat er 12% winst bij opkomt en de prijs meteen wordt afgerond op 5-tallen.

En verschillende btw-tarieven is ook wel iets om mee te nemen misschien.

Enfin, er is nog genoeg te doen, het ligt er maar net aan hoe uitgebreid je 't gaat maken. Wordt 't open-source trouwens?

[zneek]

Zou je niet de verkoopprijs in ORDER_PRODUCTS opslaan? In deze opzet verandert de prijs van oude orders als je de prijs van een artikel wijzigt.

[Verwijderd]

Okee ik heb wat aanpassingen gemaakt en schei uit met dat analoge gedoe. Hier de sql bron. Of ik hem open source maak weet ik nog niet. Ik ben hem wel van plan te bouwen op basis van PEAR (auth, conf, db, log).

PHP:

// +----------------------------------------------------------------------+
// | SQL Shop Layout 0.1                                                  |
// +----------------------------------------------------------------------+
// | Copyright (c) 2004 Koen Bok                                          |
// +----------------------------------------------------------------------+
// | Authors: Original Author Koen Bok <[e-mail]>                 |
// +----------------------------------------------------------------------+
//
// $Id:0.1:11/04/2004:shop.sql$

CREATE TABLE shop_product (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description MEDIUMTEXT NOT NULL,
    price SMALLINT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shop_variation (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    product_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_product(id),
    PRIMARY KEY (id)
);

CREATE TABLE shop_option (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    value VARCHAR(255) NOT NULL,
    price_offset SMALLINT DEFAULT '0' NOT NULL,
    variation_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_variation(id),
    PRIMARY KEY (id)
);

# Denktank: Zorg ervoor dat parent_id een bestaande category.id is, anders gaat het de soep in.
# En gaan we met een top level dir werken of niet?

CREATE TABLE shop_category (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    parent_id SMALLINT DEFAULT '0' NOT NULL,
    PRIMARY KEY (id)
);

# Many-to-many relation mapping

CREATE TABLE shop_category_map (
    category_id SMALLINT NOT NULL REFERENCES shop_category(id),
    product_id SMALLINT NOT NULL REFERENCES shop_product(id),
    PRIMARY KEY (category_id)
);

CREATE TABLE shop_user (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    login VARCHAR(32) NOT NULL,
    password CHAR(32) NOT NULL,
    email TINYTEXT NOT NULL,
    fname TINYTEXT NOT NULL,
    lname TINYTEXT NOT NULL,
    date TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE shop_order (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_user(id),
    address_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_address(id),
    discount SMALLINT DEFAULT '0' NOT NULL,
    shipping SMALLINT NOT NULL,
    tax SMALLINT NOT NULL,
    date TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE shop_order_product (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_product(id),
    order_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_order(id),
    option_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_option(id),
    PRIMARY KEY (id)
);

CREATE TABLE shop_address (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id SMALLINT UNSIGNED NOT NULL REFERENCES shop_user(id),
    street TINYTEXT NOT NULL,
    number SMALLINT NOT NULL,
    zip TINYTEXT NOT NULL,
    city TINYTEXT NOT NULL,
    state TINYTEXT,
    country CHAR(2) NOT NULL,
    phone SMALLINT NOT NULL,
    fax SMALLINT,   
    PRIMARY KEY (id)
);

[Verwijderd]

En ja, dan kom ik meteen bij de volgende vraag aan. Wat is de beste manier om de relaties vast te leggen? Zoals ik hierboven doe, of met InnoDB? Zoals ik het nu begrijp is het dat InnoDB checkt of relaties bestaan en of hij ze moet verwijderen als hun 'parent' verdwijnt, en MyIsam doet dat niet?

[thomaske]

Paar puntjes die ik even snel zie:
- Bij een order staat niet welk van de afleveradressen geselecteerd is
- Om een correcte historie te behouden (ivm prijswijzigingen) moet je de prijs bij je order opslaan (al genoemd door zneek)
- Het voorbeeld dat jij geeft dat een product meerdere variaties heeft, bijvoorbeeld kleur en maat, er wordt nu maar per order-regel 1 van die opties opgeslagen en niet 2, zoals ik zou verwachten (wordt dus een extra koppeltabel, denk ik)
- ik verwacht nog een veldje 'aantal' in je order-regel tabel (2 t-shirts)

- en misschien even nadenken over je variation-option systeem. Misschien is het makkelijker om je mogelijke variation niet afhankelijk te laten zijn van product maar van categorie

[ Voor 4% gewijzigd door thomaske op 11-04-2004 21:26 ]

[Verwijderd]

thomaske schreef op 11 april 2004 @ 21:25:
Paar puntjes die ik even snel zie:
- Bij een order staat niet welk van de afleveradressen geselecteerd is
- Om een correcte historie te behouden (ivm prijswijzigingen) moet je de prijs bij je order opslaan (al genoemd door zneek)
- Het voorbeeld dat jij geeft dat een product meerdere variaties heeft, bijvoorbeeld kleur en maat, er wordt nu maar per order-regel 1 van die opties opgeslagen en niet 2, zoals ik zou verwachten (wordt dus een extra koppeltabel, denk ik)
- ik verwacht nog een veldje 'aantal' in je order-regel tabel (2 t-shirts)

- en misschien even nadenken over je variation-option systeem. Misschien is het makkelijker om je mogelijke variation niet afhankelijk te laten zijn van product maar van categorie

- Is nu gefixt
- Heb ik nu ook
- Je hebt gelijk
- Ja ik doe er ook een many-to-many tabel tussen.

Enorm bedankt. Ik heb hem nu helemaal in MySQL staan en de relaties zijn gedefineerd mbv InnoDB. Mocht iemand interesse hebben dan moet hij even mailen.

[JaQ]

zo even een paar dingen die me nu opvallen:

- leuk dat je meerdere adressen bijhoud, maar wil je dan geen adrestype opslaan? (om in het engels te blijven: bill-to, ship-to etc.). Ga je dan ook vervolgens checken of er niet 1 ship-to en 1 bill-to is, of ga je per order opslaan naar welk adres de factuur en naar welk adres de shipment moet?

- je doet niet aan online payments? (oftewel, creditcard). Zo ja, dan zal je toch ook echt die gegevens op moeten slaan (misschien meerdere? creditcard en bankgegevens oid, uiteraard wel in een 2-way hash opslaan) Ook mis ik de payment_type codetabel.

- misschien is het wijs om je voorraad op te gaan slaan.

- levertijden zijn handig om te weten.

- mensen kunnen bestellen, maar wil je niet in je database vastleggen of er betaald is? (payment_date oid)

nofi, maar misschien is het wijs om eerst nog eens een 10-tal webshops te gaan bekijken om te zien welke features je nog meer mist. Ik verwacht dat je er nog wel een paar kan vinden. Om een lang verhaal kort te maken: volgens mij ben je leuk onderweg, maar is er nog wel e.e.a. aan analyse te verrichten.

Meer over databeesten:
Je kiest in dit geval voor MySQL. Ik ga er vanuit dat je programma straks op meerdere databases wilt kunnen gaan draaien (desnoods op *kots* access *kots*). Hou er dan goed rekening mee dat niet iedere db hetzelfde omgaat met vreemde sleutels. "Cascading constraints" (het voorkomen van weeskinderen in normaal nederlands) is niet standaard in iedere db. Het al dan niet aanwezig zijn van een procedurele taal wordt dan van belang. Als database menneke wil ik altijd zoveel mogelijk functionaliteit in de database vastleggen, maar als je db onafhankelijk wilt werken, kan je dat beter in je applicatie inprogrammeren. Relaties leggen in een database heeft dan dus geen nut. Als je toch er voor kiest om functionaliteit in je database te gaan programmeren, misschien is het dan wijs om eens naar postgresql, sapdb of firebird te kijken. (oftewel, als je het doet, doe het dan goed!)

[Verwijderd]

DrFrankenstoner schreef op 13 april 2004 @ 02:41:
zo even een paar dingen die me nu opvallen:

- leuk dat je meerdere adressen bijhoud, maar wil je dan geen adrestype opslaan? (om in het engels te blijven: bill-to, ship-to etc.). Ga je dan ook vervolgens checken of er niet 1 ship-to en 1 bill-to is, of ga je per order opslaan naar welk adres de factuur en naar welk adres de shipment moet?

- je doet niet aan online payments? (oftewel, creditcard). Zo ja, dan zal je toch ook echt die gegevens op moeten slaan (misschien meerdere? creditcard en bankgegevens oid, uiteraard wel in een 2-way hash opslaan) Ook mis ik de payment_type codetabel.

- misschien is het wijs om je voorraad op te gaan slaan.

- levertijden zijn handig om te weten.

- mensen kunnen bestellen, maar wil je niet in je database vastleggen of er betaald is? (payment_date oid)

nofi, maar misschien is het wijs om eerst nog eens een 10-tal webshops te gaan bekijken om te zien welke features je nog meer mist. Ik verwacht dat je er nog wel een paar kan vinden. Om een lang verhaal kort te maken: volgens mij ben je leuk onderweg, maar is er nog wel e.e.a. aan analyse te verrichten.

Meer over databeesten:
Je kiest in dit geval voor MySQL. Ik ga er vanuit dat je programma straks op meerdere databases wilt kunnen gaan draaien (desnoods op *kots* access *kots*). Hou er dan goed rekening mee dat niet iedere db hetzelfde omgaat met vreemde sleutels. "Cascading constraints" (het voorkomen van weeskinderen in normaal nederlands) is niet standaard in iedere db. Het al dan niet aanwezig zijn van een procedurele taal wordt dan van belang. Als database menneke wil ik altijd zoveel mogelijk functionaliteit in de database vastleggen, maar als je db onafhankelijk wilt werken, kan je dat beter in je applicatie inprogrammeren. Relaties leggen in een database heeft dan dus geen nut. Als je toch er voor kiest om functionaliteit in je database te gaan programmeren, misschien is het dan wijs om eens naar postgresql, sapdb of firebird te kijken. (oftewel, als je het doet, doe het dan goed!)

- In de eerste versie stonden ze niet ik heb ze er nu ook bij.
- De bataalmethoden die ik ga integreren ben ik nog aan het bekijken. Het mooiste zou een module systeem zijn, maar dat moet ik nog uitdenken.
- Voorraden zijn wel een handige feature, ik ga er eens over denken. Hetzelfde geldt voor levertijden.
- Dat klopt en dat is precies wat ik nu aan het doen ben. Ik heb nog geen regel code gemaakt, maar ben eerst alles aan het uitdenken met schematjes zodat ik zeker weet dat ik wat goeds maak.
- Ik heb gekozen voor MySQL met InnoDB omdat dat wijd ondersteund wordt en precies doet wat ik wil. Ik zit nog wel te twijfelen of ik de relaties zal controleren op applicatie niveau, maar het enige voordeel daarvan zou zijn in mijn optiek is dat je de shop ook op mysql<3.32 kan draaien. Aan de andere kant is het een hoop extra programmeer werk met een hoop extra queries en ik wil hem juist zo licht mogenlijk houden. Ik gebruik een DB abstraction layer (PEAR DB) dus je zou hem straks op alle gangbare databases moeten kunnen draaien mits de relaties goed gedefineerd zijn.

[Verwijderd]

Leuke thread! Ik zat laatst ook al na te denken over het scripten van een eigen webshop en dit topic is zeker weten een leuke aanvulling voor de ideeen die al in mijn hoofd zaten

Alleen 1 vraagje, stel je voor iemand flikkert 4 verschillende producten in ze shopping cart. hoe wou je dit in de orders tabel zetten? elk product een nieuw ID? aangezien je AUTO_INCREMENT op je ID hebt kan je niet verschillende entries in die tabel hetzelfde ID geven (wat IMHO ook een slordige oplossing zou zijn..). Misschien gebruik maken van een soort parent_id? Het eerste product van een reeks die in je DB komt is dan parent en de rest kindjes daarvan, zodat het in zijn geheel 1 `order' blijft. just my 2 cents..

[Creepy]

Verwijderd schreef op 13 april 2004 @ 11:48:
Leuke thread! Ik zat laatst ook al na te denken over het scripten van een eigen webshop en dit topic is zeker weten een leuke aanvulling voor de ideeen die al in mijn hoofd zaten

Alleen 1 vraagje, stel je voor iemand flikkert 4 verschillende producten in ze shopping cart. hoe wou je dit in de orders tabel zetten? elk product een nieuw ID? aangezien je AUTO_INCREMENT op je ID hebt kan je niet verschillende entries in die tabel hetzelfde ID geven (wat IMHO ook een slordige oplossing zou zijn..). Misschien gebruik maken van een soort parent_id? Het eerste product van een reeks die in je DB komt is dan parent en de rest kindjes daarvan, zodat het in zijn geheel 1 `order' blijft. just my 2 cents..

Dat is ranzig. En dan heb je een parent die eigenlijk helemaal geen parent is en moet je weer extra logica gaan bouwen die helemaal niet nodig is als je je "parent" produkt verwijdert

Kijk nog eens goed naar de tabellen En dan voornamelijk naar shop_order en shop_order_produkt. Shop_order_produkt is 1 bestelt produkt. Hierin staat een FK naar shop_order (order_id) welke de bijbehorende order is. Zo kan je dus meerdere produkten in 1 order stoppen.

[Verwijderd]

ah, doh. volgende keer ff beter opletten

[voodoo202]

De prijzen; zou je die ook niet appart op willen slaan per product.

Stel je voor, ik koop nu iets, jij hebt het niet op voorraad het wordt 2 weken later naar mij gestuurd, en tussentijds verandert de prijs.

Dan krijg ik het product dus voor de nieuwe prijs. Dit is mooi meegenomen voor mij. Maar stel je voor dat je alle aankopen in het systeem wil laten staan, voor de belastingdienst, dan kloppen de bedragen dus nooit meer, want iedere keer als een product wijzigt van prijs, dan klopt de rest van de oude facturen niet meer.

En je hebt tevens een mooie historie van de prijs stijging/daling.


Misschien is het trouwens iets om je database model in Xcase te zetten, je kunt dan heel snel een html of pdf van het ontwerp maken, en deze op internet zetten, zodat iedereen heel snel kan zien of het ontwerp goed is. Tevens kan Xcase van het model de daadwerkelijke database bouwen voor jou. En wijzigingen meteeen doorvoeren.

www.xcase.com

kun je een gratis demo versie te downloaden.

[ Voor 25% gewijzigd door voodoo202 op 13-04-2004 12:28 ]

[Verwijderd]

Ik zou wel willen, maar ik gebruik MacOS X. Ik zal eens kijken of er een alternatief is. Pen en papier gaat vermoeien ;-)

[voodoo202]

Verwijderd schreef op 13 april 2004 @ 13:36:
Ik zou wel willen, maar ik gebruik MacOS X. Ik zal eens kijken of er een alternatief is. Pen en papier gaat vermoeien ;-)

Is er geen emulator ofzo, het is opzich maar een ligt pakket.

[cameodski]

Een klein puntje wat mij opviel toen ik naar je sql script keek, was dat alle id's van het type smallint zijn. Ik neem aan dat in MySQL een smallint ook tot 65.536 gaat en dan vind ik dat je wel een risico neemt.
Als je shop in productie gaat is er voorlopig misschien niet zoveel aan de hand, maar stel dat je een leuke performance test gaat doen met bijvoorbeeld 100.000 records dan heb je al een probleempje, lijkt me.

[Verwijderd]

Wat mij opviel is dat je een 'primary key' aan `categorie_id` geeft in de tabel `shop_categorie_maps`, terwijl als je een 'primary_key' aan een veld geeft het betekent dat de inhoud hiervan maar een enkele keer mag voorkomen.

Wat ik ook vind, terugkomend op de vorige reply, dat je weinig producten hebt in vergelijking met categorieën.
Ik ben zelf namelijk ook bezig met een webshop en sta 99 categorieën toe en evenveel producten als jij doet, meer als 99 categorieën wordt denk ik erg onoverzichtelijk.

Ik hoop dat deze informatie je helpt.

Ps. Misschien kan je mij ook even helpen, wat betekent REFERENCES?
Ik kon het namelijk niet in mijn MySQL boek, van Paul DuBois, vinden. (ik vermeld het boek voor in het geval dat je het zelf ook hebt en je me de pagina kan aanwijzen.)

[thomaske]

Verwijderd schreef op 14 april 2004 @ 20:25:
Wat mij opviel is dat je een 'primary key' aan `categorie_id` geeft in de tabel `shop_categorie_maps`, terwijl als je een 'primary_key' aan een veld geeft het betekent dat de inhoud hiervan maar een enkele keer mag voorkomen.

Is inderdaad fout, de primary key moet over allebei de velden komen te liggen

Wat ik ook vind, terugkomend op de vorige reply, dat je weinig producten hebt in vergelijking met categorieën.
Ik ben zelf namelijk ook bezig met een webshop en sta 99 categorieën toe en evenveel producten als jij doet, meer als 99 categorieën wordt denk ik erg onoverzichtelijk.

Waar wordt er gesproken over de aantallen producten/categoriën in de webshop? Ik denk sowieso niet dat je zulke dingen op dit niveau al wilt beperken

Ik hoop dat deze informatie je helpt.

Ps. Misschien kan je mij ook even helpen, wat betekent REFERENCES?
Ik kon het namelijk niet in mijn MySQL boek, van Paul DuBois, vinden. (ik vermeld het boek voor in het geval dat je het zelf ook hebt en je me de pagina kan aanwijzen.)

Kijk (zoek!) eens online manual (16.7.4 FOREIGN KEY Constraints)

[Creepy]

thomaske schreef op 15 april 2004 @ 00:38:
[...]


Is inderdaad fout, de primary key moet over allebei de velden komen te liggen


[...]


Waar wordt er gesproken over de aantallen producten/categoriën in de webshop? Ik denk sowieso niet dat je zulke dingen op dit niveau al wilt beperken


[...]


Kijk (zoek!) eens online manual (16.7.4 FOREIGN KEY Constraints)

InnoDB ondersteunt ze, de standaard myisam tables echter niet.
Het verbaast me af en toe dat er mensen zijn die de fk constraints niet kennen, in zo'n beetje elke database zijn te ze implementeren (zelfs mysql met innodb ).

[Verwijderd]

Bedankt, ik had zoiets wel gevonden, maar dacht dat het niet het goede was.
Beetje raar eigenlijk, maarja...