normalising data columns that have individual dependancy

Question

The is a company that erects boards for estate agents e.g. for sale, to let boards.

when an agent posts a job, the board address, agent owning the board, and job type are all stored.

note: asterisk before attribute is primary key, asterisk after is foreign. Not all tables displayed

// background info to help understand the project more
AGENTS
*agent_id
agency_name
agency_office
address_id*

BOARDS
*board_id
client_id*
address_id*

PENDING_JOBS
*job_id
board_id*
job_type_id*
notes
submitted_on

Now I have a job type table. Each job has a:

• board_id (to addentify the client and place where the board is)
• job_type_id (whether were putting a board up, taking down, changing, adding sold slip etc)

This is my JOB TYPES TABLE

JOB_TYPES
*job_type_id
action
board
slip

and the data would look like this: (rows omitted)

JOB_TYPES
id | action       | board    | slip
-----------------------------------
1  | up           | for sale | sold
2  | up           | to let   | let by
3  | up           | for sale | null
4  | up           | to let   | null
5  | down         | null     | null
6  | service call | null     | null

I have been trying to normalise my database efficiently and according to normalisation, you are supposed to only have data in the table that depends directly on the ID e.g. agent_name and agent_office depend directly on the agent_id!

But in the job types table, the SLIP depends directly on the BOARD column, and the board column depends directly on the action column.

But surely it wouldn't be right to single out ALL the columns into separate tables with a key would it?

I guess my vague question is:

Is this okay?
Or in the real world, do people use better method or storing data like this?

Answer 1

But in the job types table, the SLIP depends directly on the BOARD column, and the board column depends directly on the action column.

Dependency is a technical term in normalization; it has a precise meaning. Here's part of your data.

JOB_TYPES
id | action       | board    | slip
-----------------------------------
1  | up           | for sale | sold
2  | up           | to let   | let by
3  | up           | for sale | null
4  | up           | to let   | null

In normalization, the question,

• "Does action determine board?" means
• "Given one value for 'action', do I know one and only one value for 'board'?"

And the answer is clearly "No". Given action = 'up', you know at least two different values for 'board': 'for sale' and 'to let'. So there's no functional dependency there.

Having said that, though, I'd have to say the table of job types does look a little odd. I suspect that you're bundling multiple, independent facts into one table, but I'd want to see more representative data before I made that judgement. (Think "put up board", "put up slip", "take down board", "take down slip".)

Bundling multiple, independent facts into one table creates a multi-valued dependency, which you can fix by normalizing to 4NF or 5NF.

Answer 2

The two rows "for sale"/sold and "for sale"/null contradict your statement that there is a functional dependency from BOARD to SLIP.