Stocker les prix d'expédition dans une table MySQL où de nombreuses colonnes seront ajoutées à l'avenir

Question

Je cherche à créer un système où les utilisateurs spécifient les prix d'expédition de leurs articles. Les variables que je dois couvrir sont la plage de poids (de en grammes à en grammes), le prix et les pays couverts. À partir de ces données, je peux calculer les frais d'expédition en consultant le pays du client et le poids total du panier.

Ma première pensée est quelque chose comme ceci:

id      from_weight to_weight   price   us  ca  gb  fr  es  de  it
------------------------------------------------------------------
1       0g          499g        1.99    Y   Y   N   N   N   N   N
2       500g        999g        2.99    Y   Y   N   N   N   N   N
3       1000g       1999g       4.99    Y   Y   N   N   N   N   N
4       2000g       2999g       7.99    Y   Y   N   N   N   N   N
5       0g          499g        4.99    N   N   Y   Y   Y   Y   Y
6       500g        999g        6.99    N   N   Y   Y   Y   Y   Y
7       1000g       1999g       9.99    N   N   Y   Y   Y   Y   Y
8       2000g       2999g       14.99   N   N   Y   Y   Y   Y   Y

Cependant, le plan serait d'ajouter de plus en plus d'options de pays. Cela signifierait ajouter plus de colonnes à chaque fois. Est-ce la meilleure façon de structurer ce type de données. D'autres suggestions ?

Answer 1

Normalement, il est préférable de laisser la structure de la table inchangée et simplement d'ajouter des lignes pour répondre au cas que vous avez illustré. (il existe des raisons telles que l'optimisation où vous pouvez dévier de cela)

Je vous suggère de chercher "3ème forme normale", si votre base de données respecte les règles pour être en 3ème forme normale, alors vous vous retrouvez généralement avec beaucoup moins de maintenance et une extensibilité plus facile par la suite.

table1
id | from_weight | to_weight | price   
1  | 0g          | 499g      | 1.99

table2
id | table1id | countrycode | status
1  | 1        | us          | Y
2  | 1        | ca          | Y
3  | 1        | gb          | N

voici comment vous interrogeriez les données

select price from table1 
join table2 on table1.id=table2.table1id
where countrycode='us' and status='Y' and
300 between from_weight and to_weight

Answer 2

Si vous souhaitez éviter d'ajouter des colonnes, vous pouvez ajouter une deuxième table qui contient priceID et Country en tant que colonnes et simplement supprimer toutes les colonnes de pays. Ensuite, vous faites simplement en sorte que priceID ait une clé étrangère faisant référence à l'ID de votre première table et vous pouvez ajouter de nouveaux pays au besoin.

Answer 3

Oui, je dirais que oui. Votre objectif va être de trouver le prix pour une combinaison de poids et de pays donnée, donc il devrait être facile d'effectuer cette requête :

SELECT price FROM table WHERE $weight > from_weight AND $weight < to_weight AND $country;

Et votre schéma le permet facilement. Je recommande que vous sélectionniez votre to_weight de telle sorte qu'une valeur de 499,9 s'insère dans l'une des catégories, à moins que le poids ne soit limité à être un entier. Je ne me préoccuperais pas d'ajouter de nouvelles colonnes, c'est facile et vous pouvez le définir par défaut sur false pour tout nouveau pays.

Answer 4

Il semble que les lignes de votre tableau forment des groupes de plans tarifaires liés. Si tel est le cas, je vous suggère d'avoir une table PricingPlan et une table PlanDetail.

PricingPlan
-----------
PricingPlanId* PlanTitle
----------------------------------------
 1              planA pour l'Amérique du Nord
 2              planB pour l'UE

PlanDetail
----------
PricingPlanId* DetailId* FromWeight  ToWeight  Price
------------------------------------------------------
 1              1            0g        499g      1.99   
 1              2          500g        999g      2.99   
 1              3         1000g       1999g      4.99  
 1              4         2000g       2999g      7.99   
 2              1            0g        499g      4.99   
 2              2          500g        999g      6.99   
 2              3         1000g       1999g      9.99   
 2              4         2000g       2999g     14.99  

Une troisième table, PlanCountry, devrait être utilisée afin que vous n'ayez pas à ajouter de nouvelles colonnes pour les nouveaux pays que vous souhaitez associer à un plan tarifaire (une nouvelle ligne serait ajoutée dans cette table, si par exemple vous voulez que le Mexique soit inclus dans le planA) :

Country
-------
CountryCode* CountryName
-----------------------------
 us           USA
 ca           Canada
 uk           Royaume-Uni
 fr           France
 es           Espagne
 de           Allemagne
 it           Italie

PlanCountry
-----------
PricingPlanId* CountryCode*
---------------------------
 1               us
 1               ca
 2               uk
 2               fr
 2               es
 2               de
 2               it
 1               mx                   --- Mexique ajouté pour planA