CODEWITHSUNDEEP
sqloracle-databaseplsqlschema
Andrej Markovic
Andrej Markovic

Reputation: 1

Oracle SQL dynamic columns

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?

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Related Questions