DB Design question

Question

User1029764681 posted

I have a table with 4 or 5 status columns.  

Trying to decide if the table that they are linked to should be 1 table or one table for each column.  I think either method would work.  

Is it okay to have the StatusID as a FK in multiple columns?

Answer 1

User475983607 posted

Is it okay to have the StatusID as a FK in multiple columns?

No

Answer 2

User753101303 posted

Hi,

Unclear. To start with it seems weird a row could have multiple status columns. All those columns could have all one of the same status value. Could it be that you try to have a kin d of status history for each row (in which case it should be likely a separate table). Alos it seems the relation is the other way round?

On my side I really don't get which real use case you are trying to implement.

Answer 3

User1029764681 posted

For example

I have a few columns like

Draft Sent to Customer

Negotiating with Customer

Final Contract Received

Each of these will have one of 4-5 status.  And they want to keep track of them separate.

Answer 4

User753101303 posted

And each column would go through which kind of status ? Seems to me for example for "Draft Sent to Customer" that it is either done or not rather than going through status ? At first sight it seems to me it should be rather date values so that you know when each miltestone is reached.

Unless it is really a fixed workflow and it's unlikely to change, I would perhaps have a table which registers for each row when each step was reached (possibly in some cases going back to a previous milestone).

Or which kind of status would you have for each of those milestones ???

Answer 5

User475983607 posted

fmrock164

For example

I have a few columns like

Draft Sent to Customer

Negotiating with Customer

Final Contract Received

Each of these will have one of 4-5 status.  And they want to keep track of them separate.

You should have at least a table containing the customerId, statusId, and date.  This table with have a row each time the status changes.

Answer 6

User-17257777 posted

Hi fmrock164,

It’s OK, if you don’t have too many status, you can have just one table to store them, and set a foreign key in each statu column. If you have many status in each statu column, for the convenience of maintenance, you had better have a table for each statu column

Best Regards,

Jiadong Meng.

Answer 7

User1029764681 posted

I was able to talk the end users into just having a Phase column, which would have one look up table.