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

  • Why not one table with the water ID, the respective additive ID and the amount as columns per row? Now joins needed, should have a decent performance with (a) index(es) on water and additive ID and is flexible if you want to introduce a new additive. – sticky bit Jan 18 at 22:50
  • 1
    Isn't it what I mentioned in the later part of the question which is shown in the smaller tables with salt_amount and sugar_amount without your respective additive ID? – calvert Jan 18 at 23:07
  • I might misunderstood your response. I apologize if that is the case. Maybe an image of a table might explains it all? – calvert Jan 18 at 23:08
  • I mean just one table not several (your "small" ones). Example DDL: CREATE TABLE water_additive (water_id integer, additive_id integer, amount integer) + FK and PK constraints and indexes omitted for brevity. – sticky bit Jan 18 at 23:17
  • 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
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
0

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

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.