Database design - which would be better?

Question

I have multiple tables.

They all have the following fields in them:

item_title | item_description | item_thumbnail | item_keywords

Would I be better off having a single items_table with an extra item_type field and then joining with the respective table, or just keep them all in separate tables?

Answer 1

Depends on the context. If your items have very little differentiation and you’re certain you’re not going to have a scenario in 6 months, 12 months, 2 years where you need items separated, then go the route of one generic “items” table. If a particular item type does have specific requirements, then you can create a separate table that contains this data and create a LEFT JOIN when querying to include the extra data.

I’d also suggest looking at other database types. Judging from your scenario (lots of item types with little variance in the data stored) I think you may benefit from a document-based database engine like MongoDB rather than a relational data-based database engine like MySQL.

Answer 2

OK, so the tables share fields. Do they also share constraints¹?

• If yes, then go ahead and merge them together.
• If not, you may keep them separate, of may merge them together, depending on what kind of tradeoff you are willing to make.

For example, if tables have separate foreign keys, you may keep them separate, or you may merge them into a single table, but keep FKs separate:

item_title
item_description
item_thumbnail
item_keywords
table1_id REFERENCES table1 (table1_id)
table2_id REFERENCES table2 (table2_id)
...
CHECK (
    (table1_id IS NOT NULL AND table2_id IS NULL ...)
    OR (table1_id IS NULL AND table2_id IS NOT NULL ...)
    ...
)

(NOTE: MySQL Doesn't enforce CHECK, so you'll need to do the equivalent enforcement from a trigger or client code, or use a different DBMS if you can.)

I'd need to know more about your database to figure out which is better.

with an extra item_type field and then joining with the respective table,

Never enforce FKs in code, if you can help it. Even if you merge the tables together, don't merge FKs, instead do something like the above. Enforcing FKs in code in the context of the concurrent environment (where multiple clients can try to modify the same data at the same time) is difficult to do correctly and with good performance - it's much better to let the DBMS do it for you.

BTW, what is item_keywords? It it's a comma-separated list of keywords (or similar), you'll need to normalize further and extract the keywords into their own separate table.

---

¹ Domain (data type and CHECK), key (PRIMARY KEY and UNIQUE) and referential (FOREIGN KEY) constraints.

Answer 3

I believe that it is good to have as less table as possible. It is easy to maintain. It is hard to imagine that if you have 3000 type of item_type. Then, there would be 3,000 different table. So single table is good idea to me in your case. In the future, when you run into situation when you need to separate the table, you can easily do so.

So the short answer, YES.

Answer 4

If i understand well, you only need to normalize your schema:

items:

item_id
item_name
item_description

items_types

item_id
type_id

types

type_id
item_file_name

So this way you can have any number of items with any number of types

Is this you want to do???

Answer 5

I would suggest you to use one table for item and one table for type for the following reasons (assume there are 10 types).

1. I am not sure which programming language you are using. As a Java developer, i will have to create each entity class for each type if I have multiple tables. So i would rather have only one class and have a type as an attribute.
2. When you have to display all of the types in the same page, you will have to execute the select query from all 10 tables for 10 types.
3. When you introduce a new type, you have to write the code to for the CRUD and Business specific operations. The developer will keep on adding the code for every new type.

Basically, if you have one table for item and one table for type, you won't have to change the database schema and code for each new type you introduce. But if you are sure that, the number of types is less and won't change, you can consider using muiltiple tables.

Answer 6

Create two separate tables and join them as per your required output.

i.e>

1.1'st TABLE (master table==>item_type)

item_type(item_type_id,item_type_name,status)

2.2'nd TABLE(child table==>item_details)

item_details(item_id,item_type_id,item_title,item_description,item_thumbnail,item_keywords)

See more examples..

Answer 7

I feel signle table would be more suitable. It will avoid more joins, complication in program(Code) and errors in compare of multiple tables. Even it will be better from the management point of view like db clustering etc.

Answer 8

If you have so many tables which needs to have the same repeated columns then yes it is a good way to create a separate table for the common fields. This is more efficient if these repeated columns are not fixed and can be changed like adding one more column to the list of common default columns.

So how could you do that?

The idea is to create a seperate table and put the common default columns there. This table is like a dummy table i.e. the columns can be added/deleted as needed.

For example-

Table - DefaultFields

Columns - item_title | item_description | item_thumbnail | item_keywords

You can then also be able to insert the values in the DefaultFields table dynamically in a loop like:

"INSERT INTO DefaultFields (item_table, item_title , item_description,item_thumbnail ,item_keywords) VALUES('"+ field.item_table + "','" + field.item_title + "','" + field.item_description+ "','" + field.item_thumbnail  + "','" + field.item_keywords)");

NOTE: field is the object that holds the values in a table wise loop.

Then further you can alter your tables to create these default fields from DefaultFields table like:

  "ALTER TABLE " + item_table+ " ADD COLUMN [" + field.item_title + "] Text"

This can be repeated for each table to alter it as needed.

In this design pattern, even if you want to:

1) add one more column or

2) delete pre existing column or

3) change pre existing column name

Then you can do so in the dummy table and the rest is updated by the ALTER table command in corresponding tables.

Answer 9

In my opinion... I would say no, never.

There is two reason for that:

• You really want to preserve a logical meaning in your database. For now it's pretty obvious for you how it's organised. But in two month (or 1 year), will it be so evident? If somebody join the project, isn't it easier for him to understand if the different logical block of your app are separated? I mean... It's true that a human and a cat are animals. Is it still logical to store both of them inside the same box?

• Performance. The shorter the table, the faster your request will be. The data will still take as much space on your disk. And i don't talk about the comparison for knowing which type of item you are looking for. I mean, if you want to select all the pages of your application, just compare the two request:

Multiple tables:

Select * from pages_tbl;

Single table:

Select * from item_tbl where type = 'page';

What will you gain from this design? No performance, no disk space, no readability. I really don't see a good reason for it.