0

Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge.

So my question here is let say I have a table to record substances in water:

enter image description here

Here I am only showing 7 columns, it could be more than 100 columns. As you can see here, those empty fields represent the researchers are not conducting tests on those. The researches will choose a few of them so basically this design will leave a lots of null fields and takes up a lot of space.

Should I normalize the large table into smaller ones like these which only have an entry when there is data being inputted? This action is called normalization, am I right? However, this way will need me to join so many tables if I want to display the data in a report.

I am using PostgreSQL and I couldn't find the maximum limit of table can a query joins online. But I have a feeling that joining too many table is a slow operation. Should I keep the full table with nulls in it or should I break it down into smaller tables? Or is there a better way to do this or how would you do it?

enter image description here

Edited:

enter image description here

Edited 2:

enter image description here

  • I added an image of the table that you described to the post. For example: if we are going to test for 100 addictive, there will be 100 rows of water_id = 1 in the table? Can we say this is a good design when there are 100 rows of same water_id? – calvert Jan 18 at 23:34
  • If you have 100 additives in the water with ID 1 yes, you'd have 100 rows. (Likely better than a 100 columns and almost surely better than 100 tables... (the latter sounds like a nightmare you never wake up from...)) – sticky bit Jan 18 at 23:40
  • Thank you for your response. I realized that I didn't ask my intended question fully, I left out one part. I posted another image on Edited 2 and let say for a certain water_id I am going to test for is_drinkable and is_dangerous, the rest of the rows will have null at the is_drinkable and is_dangerous column. Is it better off to leave it as it is or break them into smaller table? Leave it as it is will consume more space but good thing is join is not needed. – calvert Jan 19 at 0:03
  • See also this similar question: dba.stackexchange.com/questions/40850/… – Joel Brown Aug 1 at 0:46
1

As I understand you now, a "water" is kind of a recipe.

For such a recipe you want to record a) what additives where added and b) what the outcome was.

That's two entities, recipe and additives. Both have a many to many relationship with a relationship attribute, which is the amount. The outcome of a recipe is of course an attribute of the recipe and not one of a relation between a recipe and an additive. It's the whole mixture that counts.

A typical and normalized solution are three tables.

One for the additives.

CREATE TABLE additive
             (id serial,
              name text,
              PRIMARY KEY (id));

Another one for the recipe.

CREATE TABLE recipe
             (id serial,
              name text,
              drinkable boolean,
              dangerous boolean,
              PRIMARY KEY (id),
              CHECK (NOT drinkable
                      OR NOT dangerous));

Note: I added a check constraint, that a receipt deemed dangerous cannot be marked as drinkable.

And a table linking the additives for a recipe.

CREATE TABLE recipe_additive
             (recipe integer,
              additive integer,
              amount integer NOT NULL,
              PRIMARY KEY (recipe,
                           additive),
              FOREIGN KEY (recipe)
                          REFERENCES recipe
                                     (id),
              FOREIGN KEY (additive)
                          REFERENCES additive
                                     (id));
  • Thanks, now I know what to do for my project. I appreciate it. – calvert Jan 19 at 2:02
0

This one is the best idea,

enter image description here

Whether or not dangerous or drinkable is a function of the amount and the substance. That doesn't get stored at all because it's computed.

You could put the thresholds on the tables with the substance if you wanted. It's not immediately clear to me how something could be "dangerous" and "drinkable."

The name for "drinkable" is normally "potable" too, right?

  • Thanks for responding. This water testing is just an analogy which is a totally different thing than what I am trying to design. I am seeking the best practice or concept by making up with a simpler application. Assume the drinkable and dangerous column are two boolean column, and I'm inserting data into these two only for specific water_id only. What going to happen is there will be a bunch of rows with NULLs in these two columns. Should I keep the table as it is or separate the two columns from the main table so that the each of the two columns has its own table with the tested water_id. – calvert Jan 19 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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