主页 / user-279503

Lucas David Ferrero's questions

Martin Hope
Lucas David Ferrero
Asked: 2024-10-01 06:05:14 +0800 CST
  • 7

我会先介绍一些背景信息，然后再提问。我正在为一个电子商务应用程序构建数据库模型。此应用程序需要与另一个系统（ERP 系统）通信以获取产品、品牌、类别和其他与电子商务相关的信息。目前，电子商务数据库模型的设计与外部系统提供的数据类型相同。

我将与大家分享一些电子商务表格：

aikon_articulo

CREATE TABLE `aikon_articulo` (
  `aik_ar_codigo` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ar_publicarweb` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `aik_ar_descri` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ar_memo` varchar(4000) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ar_alto` double NOT NULL,
  `aik_ar_ancho` double NOT NULL,
  `aik_ar_profundo` double NOT NULL,
  `aik_ar_color` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ar_peso` double NOT NULL,
  `aik_ar_descria` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ar_mesesgarantia` double NOT NULL,
  `aik_ar_cosnet` decimal(15,2) NOT NULL,
  `aik_ap_utilidad` decimal(6,2) NOT NULL,
  `aik_ap_impuesto_interno` decimal(6,2) NOT NULL,
  `aik_iva_porcen` decimal(6,2) NOT NULL,
  `aik_stock_total` smallint unsigned NOT NULL,
  `aik_ap_precio_iva` decimal(15,2) NOT NULL,
  `aik_ar_fechamodif` bigint unsigned DEFAULT NULL,
  `aik_ar_fecha_alta` bigint unsigned DEFAULT NULL,
  `aik_fa_codigo` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ma_codigo` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_re1_codigo` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_re2_codigo` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_esa_codigo` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`aik_ar_codigo`),
  KEY `aikon_articulo_aik_fa_codigo_fkey` (`aik_fa_codigo`),
  KEY `aikon_articulo_aik_ma_codigo_fkey` (`aik_ma_codigo`),
  KEY `aikon_articulo_aik_re1_codigo_fkey` (`aik_re1_codigo`),
  KEY `aikon_articulo_aik_re2_codigo_fkey` (`aik_re2_codigo`),
  KEY `aikon_articulo_aik_esa_codigo_fkey` (`aik_esa_codigo`),
  CONSTRAINT `aikon_articulo_aik_esa_codigo_fkey` FOREIGN KEY (`aik_esa_codigo`) REFERENCES `aikon_estado_articulo` (`aik_esa_codigo`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `aikon_articulo_aik_fa_codigo_fkey` FOREIGN KEY (`aik_fa_codigo`) REFERENCES `aikon_familia` (`aik_fa_codigo`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `aikon_articulo_aik_ma_codigo_fkey` FOREIGN KEY (`aik_ma_codigo`) REFERENCES `aikon_marca` (`aik_ma_codigo`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `aikon_articulo_aik_re1_codigo_fkey` FOREIGN KEY (`aik_re1_codigo`) REFERENCES `aikon_referencia01` (`aik_re1_codigo`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `aikon_articulo_aik_re2_codigo_fkey` FOREIGN KEY (`aik_re2_codigo`) REFERENCES `aikon_referencia02` (`aik_re2_codigo`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

aikon_estado_articulo

CREATE TABLE `aikon_estado_articulo` (
  `aik_esa_codigo` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_esa_descri` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`aik_esa_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

aikon_marca

CREATE TABLE `aikon_marca` (
  `aik_ma_codigo` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aik_ma_descri` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`aik_ma_codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我只是想让你特别注意每个表中主键的数据类型。它们都是不同长度的字符串。也就是说，外部系统在主键的值中存在模式，具体取决于表。

除此之外，我听说主键的最佳数据类型是unsigned bigint。我担心为每个主键保留字符串数据类型是否是个好主意。

我应该保留原有的数据类型，还是应该将主键的数据类型更改为unsigned bigint？

我实际上并不知道这个数据库可以增长多少，但我不想将来出现意外，比如INSERT因为用完了 id 值而无法增长。这个数据库目前处于开发阶段，因此更改架构没有问题。

mysql