storing multiple formats in a table

Question

So here's the basic problem: I'd like to be able to store various fields in a database. These can be short textfields (maybe 150 characters max, probably more like 50 in general) and long textfields (something that can store a whole page full of text). Ideally more types can be added later on.

These fields are group by common field_group ids, and their type shouldn't really have anything to do with categorization.

So what's the best way to represent this in MySQL? One table with a short_text and long_text columns of differing types, one of which is to be NULL? Or is there a more elegant solution?

(I'd like this to be primarily driven by ease to select all fields with a given field_group_id.)

Clarification

I'm essentially attempting to allow users to create their own tables, but without actually creating tables.

So you'd have a 'Book' field group, which would have the fields 'Name' (short text), 'Summary' (long text). Then you would be able to create entries into that book. I realize that this is essentially the whole point of MySQL, but I need to have a LOT of these and don't want users creating whole tables in my database.

Answer 1

You could extend a base table, and have sub tables with the specific data type, like inheritance in OO.

Answer 2

I don't know exactly what you mean by "field group", but if the information (short text, long text) all belongs to a certain entry, you can create a single table and include all those columns.

Say you have a bunch of books with a title and a summary:

table: `books`
- id, int(11) // unique for each book
- title, varchar(255)
- writer, varchar(50)
- summary, text
- etc

Fields that don't necessarily need to be set can be set to NULL by default.

To retrieve the information, simply select all the fields:

SELECT * FROM books WHERE id = 1

Or some of the fields:

SELECT title, writer FROM books ORDER BY title ASC

Answer 3

What you are looking for is called an EAV. With an EAV model you can build any freaking database in the world with only inserts. But it's really horrible for a lot of reasons but yours sounds so looney-tunes, it could work.

Build an Entity table

In here you'd list

• Car
• Person
• Plant

Build an Attribute Table.

Here you'd list the PK from Entity and the list of attributes.

I'll use the word instead of a number PK.

• Car | Engine Cylinders
• Car | Doors
• Car | Make
• Person | First Name
• Person | Last Name

then in a third table you'd list the actual values for each one, again using the words but you'd have numbers.

• Car | Engine Cylinders | 4
• Car | Doors | 4
• Car | Make | Honda
• Person | First Name | Stephanie
• Person | Last Name | Page

If you want to get tricky instead on one column for value you could have 4 columns

• a number
• a varchar
• a date
• a clob

then in the Attribute table you could add a column that says which column to put the data.

If you plan on this database being "Multitenent" you'll need to add an OWNER table as the parent of the entity table, so you and I could both have a Car entity.

But this SUCKS to query, SUCKS to index, SUCKS to use for anything else but a toy app.