Geek asks and answers

Having more than 50 columns in an SQL table

I designed my database so that one of my tables contains 52 columns. All attributes are closely related to the primary key attribute. Thus, there is no area for additional normalization.

Please let me know if this situation arises, and you do not want to store as many columns in one table, which is another option for this.

+3
source share
4 answers

A table with fifty-two columns is not necessarily wrong. As others have pointed out, many databases have such animals. However, I would not consider ERP systems as examples of good data design: in my experience, they are most likely the opposite.

Anyway, move on!

You say this:

" "

, (, , BCNF). , . , ?

. , : , COL42, COL23. , COL23, , COL42. 5NF .

, 5NF. , 5NF. . , , .

0

, 50 . ERP- 100 .

, , - , (null, today ..). .

, (.. "select *" ). .

+3

, , , . . , . , . . , (), . 1 2, .

+2

Another option is to design a “Result Element Pair” (IRP) in a multi-column MCT design, especially if you will add more columns from time to time.

MCT_TABLE
---------
KEY_col(s)
Col1
Col2
Col3
...


IRP_TABLE
---------
KEY_col(s)
ITEM
VALUE


select * from IRP_TABLE;


KEY_COL ITEM VALUE
------- ---- -----
1       NAME Joe
1       AGE  44
1       WGT  202
...

IRP is a little more difficult to use, but much more flexible.

I built very large systems using the IRP design, and it can work well even for massive data. In fact, this behaves like a block of an organized database, because you only pull in the necessary rows (i.e., less input / output), and an entire wide line when you need only a few columns (i.e., more input / output).

-1
source

More articles:


All Articles