I have a database system that has 4 tables. 3 of these tables have a primary key.
The fourth table has four columns, three of which are foreign keys for the other three tables. The fourth column has data.
My question is that is this a proper design to have data in a join table(my fourth table).
Or should i create a primary key for the fourth table and make another table that uses this new primary key as a foreign key and then store the data in that table.
There's no right or wrong to this. I think it depends on what the data is. If it is a large file, I would put it elsewhere. If it regular data (varchar, number, etc), I would leave it in the join table.
As you know that any software, application evolve over a period of time and i am sure your application would be no way different. I would suggest to have a primary key (a sequence) in the fourth table as well. But you need not access it based on the primary key for the current requirement but it would be helpful in the future. So my suggestion would be to have PK as well.