关系数据库系统与动态列与动态行 [英] Relational Database System with dynamic columns with dynamic rows

问题描述

ppl，
我必须开发这样的数据库，

ppl, I've to develop a Database like this,

在这里，我有一个单词列表。我需要保持数据库中每个其他单词的相关性。当添加一个新词时，我需要添加一行和一列。

Here, I've a list of words. I need to keep relevancy for each other word in a database. when a new word added, I need to be able to add a row as well as a column.

我的一个想法就是这样，

One of my idea for this is like this,

CREATE TABLE tbl_Words
(
      [WordID]      BIGINT          NOT NULL   IDENTITY(1,1),  // This s Primary Key
      [Word]        VARCHAR(250)    NOT NULL,                  // This s Unique..
)


CREATE TABLE tbl_WordRelevancy
(
      [RelID]       BIGINT          NOT NULL   IDENTITY(1,1),   // Primary Key
      [Word1]       VARCHAR(250)    NOT NULL,
      [Word2]       VARCHAR(250)    NOT NULL,
      [Relevancy]   DECIMAL         NOT NULL,
)

但是用这个结构，如果有十万个单词，在tbl_WordRelevancy表中会有100,000 * 100,000个单词。它不好，我想。 （这个数据库可以在一天内增长到1M个字）
可以使用关系数据库结构来维护这个事情吗？或者其他维护这种结构的方法是什么。 ？

but with this structure, if there are 100,000 words, in tbl_WordRelevancy table there will be 100,000*100,000 words. Its not good i think. (This database can grow upto 1M words in one day) Is it possible to maintain this thing using Relational Database structure ? or else What are the other ways to maintain this structure. ?

推荐答案

你很接近。

CREATE TABLE tbl_Words
(
      [WordID]      BIGINT          NOT NULL   IDENTITY(1,1),  // This s Primary Key
      [Word]        VARCHAR(250)    NOT NULL,                  // This s Unique..
)

注释不要使WordID成为主键或Word独特。

Comments don't make WordID a primary key or Word unique.

CREATE TABLE tbl_Words
(
      [WordID] BIGINT  IDENTITY(1,1) PRIMARY KEY,
      [Word]   VARCHAR(250) NOT NULL UNIQUE
);

但我想你真的在寻找更多的这些线。

But I think you're really looking for something more along these lines.

create table words (
   word varchar(250) primary key
);

create table word_relevance (
    word_a varchar(250) not null references words (word),
    word_b varchar(250) not null references words (word),
      primary key (word_a, word_b),
      constraint ordered_words check (word_a <= word_b),
    relevance integer not null check (relevance between 0 and 100)
);

检查约束需要在插入之前排序单词;存储组合字1，字3和字3，字1似乎没有意义。由于您使用的是整数百分比，所以对于相关性而言，您可能比整数小于十进制。

A CHECK constraint requires ordering the words before inserting; there seems to be no point in storing both combinations "word 1, word 3" and "word 3, word 1". Since you're using whole numbers for percentages, you're probably better off with an integer than a decimal for relevance.

我不认为您很可能每天加载一百万个独特的词。牛津英语词典第二版的定义不超过17.5万字。您的目标语言可能会有所不同，但仍然存在。 。 。

I don't think you're likely to load a million unique words a day. The second edition of the Oxford English Dictionary has full definitions for less than 175,000 words. your target language may vary, but still . . .

要创建报告，请使用 PIVOT 和非常限制性WHERE子句。没有dbms要转到175,000列。我怀疑，没有人会想要阅读超过一页或更多 - 30或40列。

To create your report, use PIVOT and a very restrictive WHERE clause. No dbms is going to pivot into 175,000 columns. I suspect that no human will want to read more than a page or so--30 or 40 columns at most.

这篇关于关系数据库系统与动态列与动态行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！