Creating SQL table with array value

Question

What is the best way to create table in SQL database for my situation? Option 1 or Option 2?

TMyRecord = record
 id : integer;
 name : string;
 price : Array [0.100] of double;

If i want to create table in SQL database,

Option 1: - Creating one table and use ID and Name for all price entries.

CREATE TABLE My_Record
(
ID int,
Name varchar(255),
price float,
);

or Option 2: - use two tables and link each other?

CREATE TABLE My_Record
(
ID int,
Name varchar(255),
price float,
PRIMARY KEY (P_Id)
);
CREATE TABLE My_Record_ArrayData
(
FOREIGN KEY (ID) REFERENCES My_Record(ID)
price float,
);

Thanks in advance.

Your second option doesn't show much of a difference from the first, just more useless fluff. In both cases you have trailing commas as well as other seeming syntax errors. — vol7ron
– vol7ron, Commented Oct 27, 2014 at 3:47
You didn't mention DBMS that you'll use. For instance in PostgreSQL you could create exactly such table. — TLama
– TLama, Commented Oct 27, 2014 at 7:04
If there is a possibility of having multiple prices for the same product, go for option 2 in any other cases option 1 is for you. — mg30rg
– mg30rg, Commented Oct 30, 2014 at 10:29

radar · Accepted Answer · 2014-10-27 03:49:50Z

1

Option 2 is better, as you don't repeat Name column for each price value.

Also you just need to store ID,Name in My_Record in the second option , all price values can go to My_Record_ArrayData

another option is to go with XML data type to store all price values then you will have only one table with all values for that Name.

answered Oct 27, 2014 at 3:49

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TLama Over a year ago

XML data type ? I hope you wasn't thinking of a raw storage of XML files (how would one search by value in such overgrown structure). Even if you were thinking of some sort of (due to missing DBMS in the question) storage when DBMS stores all the values unparsed it looks like an overkill to me.

Collectives™ on Stack Overflow

Creating SQL table with array value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related