3

I need to store an unknown amount of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All fields are related to an ID.

I suppose I'd go with some kind of normalization, but what's the most optimal way? Have a table for each data type?

Thank you in advance!

5
  • "Unknown amount of fields"... this means you may add more columns related to existing data, at a later point? Or something else? Sep 11, 2012 at 16:15
  • I'm more about to have a vertical solution rather than horizontal. Or did I get you right?
    – Ivarska
    Sep 11, 2012 at 16:46
  • en.wikipedia.org/wiki/…
    – Philᵀᴹ
    Sep 11, 2012 at 17:15
  • Looks very promising, I'll read it and get back to you. :)
    – Ivarska
    Sep 11, 2012 at 19:10
  • 1
    let the user edit their own database table Sep 11, 2012 at 20:27

3 Answers 3

1

The answer depends quite a bit on what you want to do. One option is EAV as one of the notes pointed out. Another option is what I call EAV-lite which is to have stored procedures for adding columns to an additional "extended attributes" table. This gives you something of a fixed schema with extended attributes. If you need to add something else you can do it. Of course you need EAV-like catalogs to tell you which attributes to search on, which is why I call it EAV-lite.

What we did in LedgerSMB was to create two additional tables for this catalog info: custom_table_catalog and custom_field_catalog for tracking these extensions. Then the extends_* tables hold these extended attributes horizontally.

If you were on Postgres you could use hstore for these extended attributes too. In 9.1 you could even use JSON. Of course care needs to be taken in breaking the standard normalized approach here. I would recommend EAV-lite more likely.

Edit: adding sample code. Note that this works on PostgreSQL but on MySQL you will need to rewrite the functions as stored procs and possibly some other things.

CREATE TABLE custom_table_catalog (
table_id SERIAL PRIMARY KEY,
extends TEXT,
table_name TEXT
);

CREATE TABLE custom_field_catalog (
field_id SERIAL PRIMARY KEY,
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);

CREATE OR REPLACE FUNCTION add_custom_field (table_name VARCHAR, new_field_name VARCHAR, field_datatype VARCHAR) 
RETURNS BOOL AS
'
BEGIN
        perform TABLE_ID FROM custom_table_catalog
                WHERE extends = table_name;
        IF NOT FOUND THEN
                BEGIN
                        INSERT INTO custom_table_catalog (extends)
                                VALUES (table_name);
                        EXECUTE ''CREATE TABLE '' ||
                               quote_ident(''custom_'' ||table_name) ||
                                '' (row_id INT PRIMARY KEY)'';
                EXCEPTION WHEN duplicate_table THEN
                        -- do nothing
                END;
        END IF;
        INSERT INTO custom_field_catalog (field_name, table_id)
        values (new_field_name, (SELECT table_id
                                        FROM custom_table_catalog
                WHERE extends = table_name));
        EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) ||
                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' ||
                  quote_ident(field_datatype);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;
-- end function

CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR) 
RETURNS BOOL AS
'
DECLARE
table_name ALIAS FOR $1;
custom_field_name ALIAS FOR $2;
BEGIN
        DELETE FROM custom_field_catalog
        WHERE field_name = custom_field_name AND
                table_id = (SELECT table_id FROM custom_table_catalog
                        WHERE extends = table_name);
        EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) ||
                '' DROP COLUMN '' || quote_ident(custom_field_name);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

I don't know if MySQL allows for alter table statements to be done dynamically, so worst case scenario you might have to maintain these functions in the app layer.

The application then selects the contents of custom_table_catalog and custom_field_catalog and caches these in memory. When an item is saved or retrieved, these are then used to create SQL CRUD functions. Currently they are used for orders, invoices, goods, and services.

3
  • My main reason was to give users the possibility to create their own attributes; some entities might not have the same kind of attributes as others, and it doesn't feel very clever to have one table with e.g. 100 columns where 80 of them are empty.
    – Ivarska
    Sep 13, 2012 at 8:31
  • EAV seems interesting, but I'm a bit concerned about the performance. So I'm thinking about to make it a little more strict when it comes to the attributes, and not give the users the same possibility.
    – Ivarska
    Sep 13, 2012 at 8:36
  • Go with the EAV-lite solution I outlined. I can post PostgreSQL code but basically you have some stored procedures that extend an extended attributes table, and a catalog of extended attributes. The extended attributes are then stored horizontally rather than the vertical EAV model. The difference is that unlike EAV, your extra attributes are fixed for the type (by the customer) and so you trade some flexibility on input for ease and performance on output. Sep 13, 2012 at 11:03
0

Depending on what exactly you need to store, and assuming you are using SQL Server, the sql_variant datatype should work for you. This will allow you to have a key\value pair setup with the value side as a sql_variant type. Just store what the datatype actually is as a string along with the key.

Documentation on sql_variant is here: http://msdn.microsoft.com/en-us/library/ms173829.aspx

1
  • I'm using MySQL, sorry if I wasn't clear enough.
    – Ivarska
    Sep 12, 2012 at 5:35
0

Have a table for each data type?

Having a table for each data type has nothing to do with normalization. Nomralization is about not repeating base information in tables. It is valid to have a key for a row and different columns each having its own data type. In fact this is the most common way.

I need to store an unknown amount of fields.

This is a bit strange. Have you done your analysis right? If you answer is yes, then you need to tell us more so that we may be able to make accurate suggestions.

Document-based database(s) allow both of the above requirements, but I would not recommend it without understanding the problem first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.