How can several different datatypes be saved in one table

Question

This is my situation: I am constructing an ad-like application in Django and Mysql. I am using a flexible-ad approach where we have:

a table with ad categories (several categories such as home, furniture, cars, etc.)

• id_category
• name

a table with details for the ad categories (home: area, squared meters. car: seats, color.)

• id_detail
• id_category (the categ the detail describes)
• name
• type (boolean, char, int, long, etc.)

the ad table (i am selling a house. i am selling a car.)

• id_ad
• id_category
• text
• date

a table where i plan to consolidate the details of the ads (home: A-area, 500 sq-meters. car: 5 seats, red.)

• id_detail_ad
• id_ad
• id_detail
• value

Is this possible? Can I have a table of details for all the ads, even if details include numbers, texts, booleans, etc? Or would I have to save them all as text and then interpret them via code accordingly? Please express your opinions. Thank you.

Answer 1

Relational databases doesn't support user-defined data types like OODBs do. I recommend you to have the details column separated into several others columns, as you'll increase performance and future usability and scalability.

Answer 2

Consider having one table for each ad type. This is the old school RDBMS way to model the data you're describing. It means that you'll have to add a table to your database every time you add an ad type. I think you'll find this is not as bad as it sounds. The benefits of this approach will be less code written for data management and/or a better use of you object/relational mapping library (disclaimer: I've never used Django, so your mileage may vary, but this would definitely apply to other tools)

Answer 3

It's a bit of a hack, but you can store any kinds of information you don't care about indexing in a text column in mysql. You can use either pickle if you don't care about the information being readable, or (better) jsonpickle, which is human readable and easy to access with jsonpickle.encode and jsonpickle.decode. We do this at my job, and it works swimmingly.

Answer 4

These tables look an awful lot like key-value pairs... in relational database design, this is a big no-no, you want to do what Ben is recommending.

Yet I've seen that a lot of web CMMSes use this sort of arrangement in their table structures. Almost as if they expect the tables to be structured that way. If Django wants you to solve the problem this way, you might have to do it that way. In which case the other comments on pickling data in/out of columns, or using a BLOB column, works well.

However, if Django expects tables this way, quite frankly, the Django designers didn't know a thing about how to use a relational database correctly or efficiently, and their framework should use a different database engine that operates on key-value pairs. It's a poor design, if they enforce the user/programmer into situations like that.