MySQL design with dynamic number of fields

Question

My experience with MySQL is very simple. The simple stuff is simple enough, but I came across something that would require a little more knowledge. I need a table that stores a small list of words. The number of saved words can be somewhere between 1 and 15. Later, I plan to search the table with these words. I thought of several different methods:

A.) I could create a database with 15 fields and just fill the fields with zero values ​​when the data is less than 15. I don't like it very much. It seems really ineffective.

B.) Another option is to use only one field and store the data in a list separated by commas. Whenever I return to the search, I simply run the regular expression on the field. Again, this seems really ineffective.

I hope there is a good alternative to these two options. Any advice would be greatly appreciated.

-Thanks

Answer 1

C) use the normal form ; use multiple lines with corresponding keys. example:

mysql> SELECT * FROM blah;
+----+-----+-----------+
| K  | grp | name      |
+----+-----+-----------+
|  1 |   1 | foo       |
|  2 |   1 | bar       |
|  3 |   2 | hydrogen  |
|  4 |   4 | dasher    |
|  5 |   2 | helium    |
|  6 |   2 | lithium   |
|  7 |   4 | dancer    |
|  8 |   3 | winken    |
|  9 |   4 | prancer   |
| 10 |   2 | beryllium |
| 11 |   1 | baz       |
| 12 |   3 | blinken   |
| 13 |   4 | vixen     |
| 14 |   1 | quux      |
| 15 |   4 | comet     |
| 16 |   2 | boron     |
| 17 |   4 | cupid     |
| 18 |   4 | donner    |
| 19 |   4 | blitzen   |
| 20 |   3 | nod       |
| 21 |   4 | rudolph   |
+----+-----+-----------+
21 rows in set (0.00 sec)

, group_concat. , K. grp, . , .

Answer 2

?

. , . - . , , -URL, :

CREATE TABLE WebPage (
    ID INTEGER NOT NULL,
    URL VARCHAR(...) NOT NULL
)

:

CREATE TABLE Words (
    Word VARCHAR(...) NOT NULL,
    DocumentID INTEGER NOT NULL 
)

. , :

SELECT Words.Word FROM Words, WebPage 
WHERE Words.DocumentID = WebPage.DocumentID
AND WebPage.URL = 'http://whatever/web/page/'

, :

SELECT WebPage.URL FROM WebPage, Words
WHERE Words.Word = 'hello' AND Words.DocumentID = WebPage.DocumentID

.

Answer 3

Hurpe, , , , 15 . , , , ?

, ? " " .

, , WORD, 15 , :

ID             int
Word           varchar(100)

CAR :

ID              int
Name            varchar(100)

CAR_WORD " ":

ID              int
CAR_ID          int
WORD_ID         int

WORD:

ID   Word

001  Family
002  Sportscar
003  Sedan
004  Hatchback
005  Station-wagon
006  Two-door
007  Four-door
008  Diesel
009  Petrol

CAR

ID   Name

001  Audi TT
002  Audi A3
003  Audi A4

CAR_WORD :

ID    CAR_ID   WORD_ID
001   001      002
002   001      006
003   001      009

Audi TT .

, , SQL :

SELECT c.name
FROM CAR c
INNER JOIN CAR_WORD x
ON c.id = x.id
INNER JOIN WORD w
ON x.id = w.id
WHERE w.word IN('Petrol', 'Two-door')

! , , , .

Answer 4

, A . B , ( ). , , 1NF.

.

Answer 5

, . . , 15- , , , - , . , (). varchar . ( ) .

Answer 6

15 15 , . , , , : " 20...", , .

Answer 7

, :

• : , ( ?) . , " , ", " , " " " ".

  CREATE TABLE ( id INT NOT NULL AUTO_INCREMENT , string TEXT NOT NULL )

  CREATE TABLE ( id INT NOT NULL AUTO_INCREMENT , VARCHAR (14) NOT NULL UNIQUE, ( ASC) )

  CREATE TABLE word_string ( id INT NOT NULL AUTO_INCREMENT , string_id INT NOT NULL, word_id INT NOT NULL, word_order INT NOT NULL, FOREIGN KEY (string_id) (string.id), FOREIGN KEY (word_id) (word.id), INDEX (word_id ASC) )

  // INSERT INTO string (string) VALUES (' '), ( " " )

  INSERT INTO word (word) VALUES ('this'), (' '), ( ''), (''), ( ''), (''), (''), (''), ( ''), ( ''), ( '')

  INSERT INTO word_string (string_id, word_id, word_order) (0, 0, 0), (0, 1, 3), (0, 2, 4), (1, 3, 1), (1, 4, 2), (1, 5, 3), (1, 6, 4), (1, 7, 5), (1, 8, 7), (1, 9, 8), (1, 10, 9)

  // - , "" "", UNIQUE string.id, string.string INNER JOIN word_string ON string.id = word_string.string_id INNER JOIN word AS fox ON fox.word = 'fox' word_string.word_id = fox.id INNER JOIN word AS quick ON quick.word = 'quick' word_string.word_id = word.id