Decimal and varchar or just one varchar

Databases
1

Hi,

I have a table for storing prices. the value may be 123.00 or it may be ‘unsuitable for children’, for example.

Would you store the ‘price’ value in one column in the datatype varchar, or would you have two columns where one is decimal and the other is varchar?

I began with two cols (they’re still in the table), but, as I have progressed, it seems to me that one varchar column would be better.

So your advice and reasoning would be very welcome.


CREATE TABLE IF NOT EXISTS events_prices
( event_id int(20) NOT NULL
, hours_id bigint(11) NOT NULL
, price decimal(7,2) default NULL
, text_price varchar(99) default NULL
, age_group varchar(99) NOT NULL
, KEY event_prices_hours_fk (hours_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

bazz

2

Thank you guys.

I have opted for two columns.

bazz

3

A single column doesn’t make sense to me. I’m not sure why the rows would be in the database if they don’t have a price.

In any event, an index on one VARCHAR column is going to have an infinitely large cardinality (estimated number of unique values) compared to a numeric column for the price and a boolean column for “unsuitable_for_children” which would have a cardinality of 2.

4

They are still two different things and neither of them means “no entry” - you’d need to define that separately either as a third field, a separate table or in the program logic.

Anyway, doesn’t the product still have a price even if it isn’t suitable for children (under what age?)

Perhaps two fields, one for price and one for minimum suitable age would be the most effective.

5

Thanks both. now I think I must have been wrong :frowning:

@rudy, why?
well, I considered the value to be an admission conditional which means the condition on which entrance is permitted is whatever that value may be. ‘unsuitable’ means no entry and 25.00 means you can come in. whilst one is numeric tha other is text but they sort of fell into the same category when I was thinking like that.

6

why?

7

I would store the price as a decimal data type and have another column to store whether the item is suitable for kids or not.

Then you can customise your queries to retrieve or update just the records you want according to price and/or suitability.