CODEWITHSUNDEEP
sqldatabasedatabase-design
vietean
vietean

Reputation: 3033

There are advantage and disadvantage between using one table or separating multi tables?

I have two options about database design.

Edit:
It is used for storing the values of an item.
The value can be: INTEGER or DOUBLE or TEXT or VARCHAR...
End edit

My question is: What are advantages/disadvantages between them? In the context that I want to store dynamic form values like Google Form (docs.google.com).

Option 1:

Table: value
Column:     item_id     value_int       value_varchar       value_text      value_double
Datatype:   INTEGER     INTEGER         VARCHAR(255)        TEXT            DOUBLE

Option 2:

Table: value_int
Column:     item_id     value
Datatype:   INTEGER     INTEGER


Table: value_varchar
Column:     item_id     value
Datatype:   INTEGER     VARCHAR(255)

Table: value_text
Column:     item_id     value
Datatype:   INTEGER     TEXT

Table: value_double
Column:     item_id     value
Datatype:   INTEGER     DOUBLE

Upvotes: 2

Views: 1721

Answers (3)

Rohan Prabhu
Rohan Prabhu

Reputation: 7302

The second option is not really an option. What it is, is a horrendous mistake. Why would you do something like that? There is not a single advantage I can think of for the second case. If you would like to, for example get more than one value at a time, in the first case you would just select a row (or a subset, thereof). In the second case you would be performing unnecessary JOINS or even worse, multiple queries (ok, I accept, I don't really know which would be worse, but both of them are unspeakably bad). You are unnecessarily complicating the system design in the 2nd case.

Are you trying to store something like a general value i.e. a value that could be a double or a float etc., like a variant? In that case, you are better off making different tables for double, int etc. and storing the type and id of the respective table in each row. This is something I would still not recommend, and would just ask you to go with the first option, but you may want to do this in case storage requirements are your concern.

The second option, as compared to the first one, offers absolutely no advantage and is rather indicative of a bad design.

EDIT

You have two options here. Go with the first one, or, you can create tables called 'double', 'int', 'string' etc. In the main table, only and have 3 columns, 'value_name', 'value_type', 'value_id'. If I am to put in let's say, x = 3.1, then put 3.1 in the table 'doubles'. Let's say the table looks like:

table `doubles`
id  value
1   3.1

This id should be dependent only and only on the table 'doubles' and not otherwise. In the main table, the entry should be:

table `main`
value_name  value_type  value_id
x           double      1

The value_type and value_id should pinpoint you to the correct value. The problem with this table is that you will need atleast 2 queries to get to a value. Hence, if storage isn't really a very big concern, you could very well use your first option wherein data can be received within a single query.

What you could also do is to store the data in raw format (directly write bytes), but it would create platforms depending on the architecture, but mostly it will completely disable you from using conditional clauses, indices etc. So, I would absolutely not recommend this method.

In the second option that I told you, you store only as many doubles, ints and strings as required. In the first option, we store all with the added convenience that we can get data immediately without multiple queries or expensive JOINs. In your second option, neither is there a storage trade-off nor can you get the data without multiple queries or expensive JOINs.

Upvotes: 2

Emile Bergeron
Emile Bergeron

Reputation: 17430

You may want to take a look at database normalization.

First option can't be bad since we don't know what will be the data inside. Second option, I don't think that it's a good idea to make a table for each data and pair it with an id. You could have a table full of data type and one table that link id to generic data like Place name, company names, etc.

Edit because of your edit

So if it is just an item table that you want, I would go with Option 1, since option 2 is duplicating multiple times the ID. Option 2 will also add lot of useless links between tables.

Upvotes: 0

Malk
Malk

Reputation: 12003

Without context I would say that option 1 is better as I am inferring that you expect a value in each of these for a given item.

If not then I believe you are getting into the 4th normalization form which says that you should extract them to separate tables. However in practice most people don't normalize that far. http://en.wikipedia.org/wiki/Fourth_normal_form

Upvotes: 1

Related Questions