Structurer sa base de données pour des prix dégressifs

Publié par Maxence le mer, 09/09/2009 - 10:12

Les prix dégressifs sont courants dans le commerce électronique (tout comme dans le commerce traditionnel d'ailleurs). L'idée, c'est que le prix diminue en fonction des quantités demandées. Cela peut également s'appliquer aux tarifs de livraison : plus le poids augmente, plus les frais de livraison augmentent. Par exemple, voici des prix dégressifs pour des tirages photos :

Prix des tirages photos
de 1 à 1000,14 €
de 101 à 2000,12 €
plus de 2000,10 €

Structure de la base de données

On a donc plusieurs tarifs pour un même produit. Dans le cadre d'une base de données relationnelle, ça correspond à une relation un-vers-N. On en déduit qu'on a besoin de deux tables. Par exemple :

CREATE TABLE `products` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(64) NOT NULL,
  `price` decimal(10,3) NOT NULL,
  `price_range` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 
CREATE TABLE `products_prices` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` smallint(5) unsigned NOT NULL,
  `frm` double NOT NULL,
  `price` decimal(10,3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 
ALTER TABLE `products_prices` 
  ADD FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

Dans la table products, on a une colonne price_range qui est en fait un booléen. Si à 0, l'article a un prix fixe qui ne varie pas suivant la quantité commandée. Le prix est alors enregistré dans la colonne price. On utilise un type DECIMAL avec une précision de 3. Sur les articles à faible prix unitaire, on a souvent des prix avec 3 chiffres derrière la virgule. Les produits qui ont des prix dégressifs auront une colonne price_range à 1. Les prix pour chaque intervalle seront enregistrés dans la table products_prices.

Cette table comporte une clé étrangère (product_id) qui permet de lier les prix aux articles. Pour renforcer l'intégrité de la base de données, on ajoute une contrainte. La clause ON DELETE CASCADE permet de supprimer automatiquement les prix associés à un article lorsqu'on supprime celui-ci.

Pour spécifier l'intervalle, on a besoin uniquement de la borne inférieure. La borne supérieure pourra être déduite à partir de l'intervalle suivant. J'utilise frm (from) comme nom de colonne pour éviter les problème puisque FROM est un mot réservé du langage SQL.

Une fois ces tables mises en place et après avoir ajouté quelques données, on peut retrouver le prix d'un article en fonction de la quantité demandée à l'aide de la requête suivante :

SELECT 
  `price`
  FROM `products_prices`
  WHERE `product_id` = 15
  AND `frm` <= 60
  ORDER BY `frm` DESC
  LIMIT 1

Ici, on recherche le prix du produit N° 15 pour une quantité commandée égale à 60. On ne retient pas les prix dont la borne inférieure est inférieure ou égale à la quantité commandée à l'aide de la clause `frm` . On ordonne ensuite les prix et on limite la requête à un résultat. On obtient de cette façon le prix associé à la borne la plus proche de notre quantité.

Au niveau des performances, on utilise une clause ORDER BY, ce qui peut être couteux en temps de traitement, mais ce n'est pas gênant ici, car en pratique les grilles de prix contiennent rarement plus d'une dizaine d'entrées (sinon, ça deviendrait trop compliqué pour présenter à un client).

Implémentation dans l'ORM

Je suppose que vous connaissez le terme ORM (Object-Relational Mapping), mais je vais quand même en rappeler la définition. C'est une couche applicative qui permet la manipulation d'une base de données par l'intermédiaire de la programmation orienté objet. Chaque ligne d'une table de la base de données peut être accédée par le biais d'un objet. C'est donc l'endroit idéal pour placer une abstraction qui va dissimuler la relative complexité de notre schéma.

Voici un exemple d'implémentation avec Zend_Db. On va simplement ajouter une méthode à une classe dérivée de Zend_Db_Table_Row_Abstract. Cette méthode prend en paramètre la quantité et retourne le prix unitaire de l'article.

<?php
class Default_Model_Products_Row extends Zend_Db_Table_Row_Abstract
{
    /**
     * Retourne le prix unitaire de l'article pour la quantité indiquée
     *
     * @param float $qty
     * @return float 
     */
    public function getPrice($qty)
    {
        if ($this->_data['price_range']) {
            $prices = new Default_Model_Products_Prices();
            $select = $prices->select();
            $select->from($prices, array('price'))
                   ->where('`product_id` = ?', (int) $this->_data['id'])
                   ->where('`frm` <= ?', (float) $qty)
                   ->order('frm DESC')
                   ->limit(1);
            $price = $this->_table->getAdapter()->fetchOne($select);
            if (empty($price)) {
                throw new Exception("Price not found for product #{$this->_data['id']}");
            }
            return $price;
        } else {
            return (float) $this->_data['price'];
        }
    }
}
?>

Le code est assez simple. Si price_range est à 1, on récupère le prix à partir de la table products_prices. Il peut arriver qu'aucun prix ne soit associé au produit. La méthode lève alors une exception. Il est préférable en effet d'avertir l'utilisateur qu'aucun prix n'a été défini pour ce produit. Si le produit n'a pas de prix dégressifs, on retourne tout simplement la valeur contenue dans la colonne price. Dans ce cas, on ne tient pas compte de la quantité commandée.

Voilà c'est tout pour ce petit patron de conception SQL (qui porte peut-être un nom, je compte sur vous pour le trouver). En dehors des prix dégressifs, il peut être aussi utile pour des données qui varient dans le temps comme le montant d'un salaire par exemple. La borne est alors une colonne de type DATE.

Autres articles sur le même sujet

Etiquettes:

Ajouter un commentaire