Oracle SQL dynamic columns

Question

I want to create table with expanding columns ,depending on user activity. If i have tables Project,Worker and Worker_Projects , that looks like this:

Worker_project: Worker name , list of project names as column. Every time i add the project i want to alter the table and add column, but the problem is that PL SQL does not support some static SQL including Alter.

What other solutions do i have?

Gordon Linoff · Accepted Answer · 2017-10-29 01:13:09Z

2

Wrong approach. Right list of tables, but wrong approach.

create table WorkerProject (
    WorkerId int not null references Workers(WorkerId),
    ProjectId int not null references Projects(ProjectId)
);

This is the basic idea. You don't really give the layout of your tables, but the idea is that you add a new row, not a new column. This is called a junction table.

You can add additional information into the table as well. For instance, the date the worker was assigned, who made the assigned, and so on.

I should point out that Oracle is a powerful database that offers other solutions, such as nested tables or JSON encoding. First, though, you should master the basics of how to store relational databases in the SQLish way. If you have specific needs, then there is additional functionality that can help.

answered Oct 29, 2017 at 1:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL dynamic columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related