-1

I have a set of processing priorities that change every day given some user inputs in another program. Each priority has an item and a set of names assigned to it. The names can duplicate across priorities and there usually is multiple names per priority. Is there a better way to organize my priorities table? Am I using the array for the right reasons? Note that field name_id in my priorities table is an array. The array can be 2500 elements long. I am doing this in PostgreSQL. Happy to supply more details.

Table of names:

+---------+------+
| name_id | name |
+---------+------+
|       1 | bob  |
|       2 | joe  |
|       3 | ben  |
+---------+------+

Table of items:

+---------+--------+
| item_id |  item  |
+---------+--------+
|       1 | sticks |
|       2 | stones |
|       3 | cars   |
|       4 | buses  |
+---------+--------+

Table of priorities:

+----------+---------+---------+
| priority | item_id | name_id |
+----------+---------+---------+
|        1 |       1 | [2,3]   |
|        2 |       3 | [1]     |
|        3 |       4 | [1,2,3] |
+----------+---------+---------+
4

This is not following the First Normal Form (1NF). Your priorities table should contain a row per priority, item_id, and name_id:

+----------+---------+---------+
| priority | item_id | name_id |
+----------+---------+---------+
|        1 |       1 |       2 |
|        1 |       1 |       3 |
|        2 |       3 |       1 |
|        3 |       4 |       1 |
|        3 |       4 |       2 |
|        3 |       4 |       3 |
+----------+---------+---------+

This will make it easier to enter or remove a name_id from a priority/item_id combination. All 3 fields should make up the primary key to prevent a double entry.

If you need the name_id in an array then you can always create a view for that purpose.

  • I thought something was wrong there. Thanks. Glad I posted. Also, on your edit of my question, what do you use to put the tables in one grey block like that? – mountainclimber Sep 21 '16 at 14:50
  • For the grey blocks you put in an empty line and then 4 spaces in front of every line. – Marco Sep 21 '16 at 15:04

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.