CODEWITHSUNDEEP
mysqlsqldatabasemultiple-columns
John Bergqvist
John Bergqvist

Reputation: 1003

Can I create a table structure with dynamic columns in MySQL?

I've created a stock control database which contains two tables (actually more than two, but these are the two that are relevant to my question): Stock, and Receipts

I would like the link between the stock in the stock table,and the stock in the receipts table to be a little more clearer, this would be fine if a customer could only order one item of stock per receipt, as i'd simply have a StockID column and a Quantity column in the Recipts table, with the StockID column as an FK to the ID in the Stock table, however, the customer can make a receipt with any number of items of stock on it, which would mean i'd have to have a large number of columns in the Receipts table (i.e. StockID_1, Quantity_1, StockID_2, Quantity_2 etc.)

Is there a way around this (can you have like a dynamically expanding set of columns in MySQL) within MySQL, other than what i've done at the moment, which is to have an OrderContents column with the following structure (which isn't enforced by the database or anything) StockID1xQuantity,StockID2xQuantity and so on?

I would post an image of the DB structure, but I don't have enough repuation yet. My lecturer mentioned something about that it could be done, by normalising the database into 4th or 5th normal form?

Upvotes: 0

Views: 1901

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 563021

No, relational databases do not allow dynamic columns. The definition of a relational table is that it has a header that name the columns, and every row has the same columns.

Your technique of repeating the groups of stock columns is a violation of First Normal Form, and it also has a lot of practical problems, for instance:

  • How do you know how many extra columns to create?
  • How do you search for a given value when you don't know which column it's in?
  • How do you enforce uniqueness?

The simplest solution is as @OGHaza described, store extra stock/quantity data on rows in another table. That way the problems above are solved.

  • You don't need to create extra columns, just extra rows, which is easy with INSERT.
  • You can search for a given value over one column to find it.
  • You can put constraints on the column.

If you really want to understand relational concepts, a nice book that is easy to read is: SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date.

There are also situations where you want to expand a table definition with dynamic columns that aren't repeating -- they're just new attributes. This is not relational, but it doesn't mean that we don't need some data modeling techniques to handle the scenario you describe.

For this type of problem, you might like to read my presentation Extensible Data Modeling with MySQL, for an overview of different solutions, and their pros and cons.

PS: Fourth and Fifth normal form have nothing to do with this scenario. Your lecturer obviously doesn't understand them.

Upvotes: 0

OGHaza
OGHaza

Reputation: 4795

I'd suggest having 3 tables:

Stock (StockID) + stock specific fields

Receipt (ReceiptID) + receipt specific fields.

StockReceipt (ReceiptID, StockID, Quantity) (could have a StockReceiptID, or use StockID+ReceiptID as Primary Key)

A solution including prices could look like:

Stock (StockID, Price)

PriceHistory (StockID, Price, Date) or (DateFrom, DateTo)

Receipt (ReceiptID, ReceiptDate)

StockReceipt (ReceiptID, StockID, Quantity)

That way you can calculate TotalStockReceiptPrice and TotalReceiptPrice for any receipt in the past.

I suspect this might be what you're looking for:

Stock (StockID, StockPrice)

Receipt (ReceiptID)

StockReceipt (ReceiptID, StockID, Quantity) 

SELECT r.ReceiptID, SUM(s.StockPrice * sr.Quantity) AS ReceiptPrice
FROM Receipt r
INNER JOIN StockReceipt sr ON r.ReceiptID = sr.ReceiptID
INNER JOIN Stock s ON sr.StockID = s.StockID
GROUP BY r.ReceiptID

This is all very normalised (again, no idea to what normal form - 3rd?). However it only works if the StockPrice on the Stock record NEVER changes. As soon as it changes your ReceiptPrices would all reflect the new price instead of what the customer actually paid.

If the price can change, you'd need to either keep a price history table (ItemID, Price, DateTo, DateFrom) or record the StockPrice on the StockReceipt record (and then get rid of the JOIN to the Stock record in the above query and make it use sr.StockPrice instead of s.StockPrice)

To do the INSERT you posted below, you'd have to do:

INSERT INTO StockReceipts  (ReceiptID, StockID, Quantity, TotalStockPrice) 
SELECT 1, 99, 2, s.StockPrice
FROM Stock s 
WHERE s.StockID = 99

However it's quite likely that whatever is issuing this receipt (and triggers the INSERT) already knows the price so could just insert the value.

Upvotes: 1

Related Questions