Relational Database System with dynamic columns with dynamic rows

Question

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

enter image description here

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,
)

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. ?

Where are you getting a million unique words a day? What language? — Mike Sherrill 'Cat Recall', Sep 15 '11 at 00:49

score 1 · Answer 1 · answered Sep 14 '11 at 05:05

What you actually want is called a many to many relationship between words.

CREATE TABLE words (
  word VARCHAR(250) PRIMARY KEY
);

CREATE TABLE word_relevancies (
  leftword  VARCHAR(250) REFERENCES words,
  rightword VARCHAR(250) REFERENCES words,
  relevance DECIMAL NOT NULL,
  PRIMARY KEY (leftword, rightword)
);

In your original formulation, you mistook a relationship for a table. This is a common step in learning how to think in sets.

I strongly suggest you get a book on relational database design, such as Joe Celko's SQL for Smarties or Thinking in Sets and familiarize yourself with proper database design. You'll save yourself a lot of pain.

Isn't your table "word_relevancies" essentially the same as the OP's "tbl_WordRelevancy"? I'll grant that he had an incorrect implementation of a M:N relationship (among other things), but it looks to me like he's on the way to a M:N relationship with that table anyway. — Mike Sherrill 'Cat Recall', Sep 15 '11 at 00:48

score 1 · Accepted Answer · answered Sep 15 '11 at 01:15

You're close.

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

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)
);

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.

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 . . .

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.

Relational Database System with dynamic columns with dynamic rows

2 Answers2

Linked