CODEWITHSUNDEEP
databasedatabase-designmultidimensional-array
teddy
teddy

Reputation: 217

Save 2-dimensional spreadsheet data to database

I have several tables of weight/price pairs with the following format:

(example for two tables, I have several such tables)

    table 1:               table 2:
weight    price         weight    price
  5         7             5         4
  10        9             10        7
  15        14            15        11
  20        15            20        14
  ...                     ...

Each weight/price table has the same amount of rows but it should be possible to edit the values when needed. It should also be possible to add new tables at a later time with as little trouble as possible.

I would like for the table to be almost like an attribute of another entity. Is there a way to do such a thing? Some suggest to simply store the files on the disk and read them when needed, but that solution is not perfect for me, since I would like to edit the values on occasion.

What is the correct structure to store such data?

Upvotes: 0

Views: 162

Answers (2)

emperorz
emperorz

Reputation: 427

Would it work for you to have a "table number" field? So it becomes:

product_id,
table_number,
weight,
price

With a primary key on product_id, table_number.

Now it isn't a "table number" any more, but more a "pair number", or similar, so you could change the names to be more meaningful.

Upvotes: 0

nvogel
nvogel

Reputation: 25534

In SQL you could simply do something like this (assuming "Product" is the thing that identifies each set of weight/price pairs and assuming weight determines price):

CREATE TABLE ProductWeightPrice
 (Product VARCHAR(20) NOT NULL,
  Weight INT NOT NULL,
  Price INT NOT NULL,
  PRIMARY KEY (Product,Weight));

If you need more help then please say what DBMS you are using.

Upvotes: 1

Related Questions