1 or 2 tables - best practice

[nunomira]

Hi,

For example's sake, lets suppose I have an applications where each entry is a project.

Each and every project has an id, title and text.

Some of the projects (but very few) also have "some more info": id, id_project, date and place.

Should I have the two tables described above (projects and more_info) or should I have one table only? I thought that one table only would be a bad Idea because it would end up with a lot of empty fields as date and place are rarely populated.

Having these two tables, I can use a LEFT JOIN in MySQL to get all the results as if it would be one table only.

I look at it as a one to few relationship. Does this relationship exist?

What are your thoughts on this?

[Piranha]

As I understand it you don't have a one-to-many relationship no matter what way you decide to go, you have a one-to-one relationship. That is of course if there can only be 0 or 1 id, id_project, date and place for one particular project. If it is like that I would use 1 table. If you mean that one project can have many dates and places then it is a one-to-many relationships and should defenetley be done with two tables.

It is true that you normally should not have NULL values in your database to keep it as "clean" as possible. But there is no reason to overdo it, and usually I don't care that I leave fields that is usually empty. When you get to queries that take time it is much better to do it this way than having to use even more time with additional joins.

at the same time joins take time, and it is no reason to overdo it.

[nunomira]

Hi,

yes, that's right, it's a one-to-one relationship, but one-to-one, at best. That's why I came up with the "one-to-few".
🙂

I see what you mean. 2 tables complicate things, and the LEFT JOIN is always needed, so it's probably better stick with one table only.

Thanks.

[Sxooter]

plus, normally nulls don't take up much space. In MySQL I think they take a byte, and in postgresql they take a single bit, and several null fields will all fit in the same byte.

[nunomira]

ok, thanks.

So I have another question regarding NULL:

Supposing that the fields that will not be filled are VARCHAR and TEXT, should they be NULL? Is it preferable than an empty string? Because usually i'd make them NOT NULL.
What about if the field is a TINYINT?

[Sxooter]

If they are something that might not have data in them, then they should be nullable. Null is generally prefereable to an empty string, because they generally use up less space, and they don't use up as much / any space in an index if you are indexing that field.

As the app designer, you decide what NULL means. In most instances it means "we don't know" but for this table, it would mean "no applicable entry for this record"

[nunomira]

cool, thanks.
🙂