表示关系数据库表中的多维数组数据？ [英] Denoting multi-dimensional array data in relational database table?

问题描述

说我有一个像这样的对象样数据记录：

Say I have an object-like data record like this:

$article = array(
    'title' => '',
    'tagline' => '',
    'content' => '',
    'stats' => array(
        'words' => 0,
        'paragraphs' => 0,
        'tables' => 0
    ),
    'references' => array(
        'reference 1',
        'reference 2',
        'reference 3'
    ),
    'attachments' => array(
        'images' => array(
            'image 1',
            'image s'
        ),
        'videos' => array(
            'video 1',
            'video 2'
        )
    )
);

我的问题是如何将这个数据记录数组存储在关系数据库中？我应该如何设计表结构？

My question is how can I store this array of data record in relational database? How should I design the table structure?

我知道我总是可以设置平面字段，如stats_words，stats_paragraphs等等，但是有更多的结构性方法吗？而不是在单个字段中存储JSON或序列化的字符串....

I know I can always set up flat fields such as stats_words, stats_paragraphs, and so forth but is there any more structural ways? Instead of storing a JSON or serialized string in a single field....

谢谢！

推荐答案

例如：

article
  ID
  title _
  tagline _
  content ___
  stat_words
  stat_paragraphs
  stat_tables

article_reference
  ID
  article_id -> article
  reference _

article_attachment
  ID
  article_id -> article
  att_type // image or video
  path _
  title _

（ _ 表示varchar /文本字段，其他字段是数字）

(_ means varchar/text fields, other fields are numbers)

p>

Or as MySQL DDL:

CREATE TABLE IF NOT EXISTS article (
   id               INT NOT NULL AUTO_INCREMENT,
   title            VARCHAR(255) NOT NULL,
   tagline          VARCHAR(255) NOT NULL,
   content          MEDIUMTEXT NOT NULL,
   stat_words       INT NOT NULL,
   stat_paragraphs  INT NOT NULL,
   stat_tables      INT NOT NULL,
   PRIMARY KEY ( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS article_reference (
   id               INT NOT NULL AUTO_INCREMENT,
   article_id       INT NOT NULL,
   reference        VARCHAR(255) NOT NULL,
   PRIMARY KEY ( id ),
   FOREIGN KEY ( article_id ) REFERENCES article( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS article_attachment (
   id               INT NOT NULL AUTO_INCREMENT,
   article_id       INT NOT NULL,
   att_type         INT NOT NULL,
   path             VARCHAR(255) NOT NULL,
   title            VARCHAR(255) NOT NULL,
   PRIMARY KEY ( id ),
   FOREIGN KEY ( article_id ) REFERENCES article( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

这篇关于表示关系数据库表中的多维数组数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！