New table/design question

[TysonLPrice]

I’m a programmer with just a smattering of SQL database background which is mainly accessing the predesigned databases. I’m changing an application which involves adding a new table and relationship. It’s basically this: There is a single table in currently in place that contains information about users such as departments login information, certain privileges, etc. There is only one entry per user. I’m only interested in three columns at this point.

CurrentTable
UserID Company Department

User1 fkCompany fkDepartment
User2 fkCompany fkDepartment
User3 fkCompany fkDepartment

The requirement is that for one process the user can be in multiple companies and departments for a certain function. What I have in place in test is a table like this:

NewTable
UserID Company Department

User1 fkCompany fkDepartment
User1 fkCompany fkDepartment
User2 fkCompany fkDepartment
User2 fkCompany fkDepartment
User2 fkCompany fkDepartment
User3 fkCompany fkDepartment
User3 fkCompany fkDepartment

The logic in the program is:

Select UserID, fkCompany, fkDepartment, 1 ‘Source’ from CurrentTable
Where UserID = ‘XXX’
Union
Select UserID, fkCompany, fkDepartment, 0 ‘Source’ from NewTable
Where UserID = ‘XXX’
Order by Source Desc -- Keeps the primary Company and Department first in the returned rows

I’ll need to set up a maintenance screen for the new table.

Someone mentioned I should be creating a join/junction table for this. I’ve looked on-line at the concept but I don’t see why I need one. I’d like some feedback on this before I go much further since it is a new process. The CurrentTable is used extensively, always expects one row per user, and changing it is not an option.

 

[simian336]

Guess I am still fuzz on your goal.

Your query would produce the following result.

UserID Company Department Source
User1 fkCompany fkDepartment 1
User1 fkCompany fkDepartment 0
User1 fkCompany fkDepartment 0

Where as a join, something like....

Select a.UserID, a.fkCompany, a.fkDepartment, 1 as 'ASource',
b.UserID, b.fkCompany, b.fkDepartment, 0 'BSource'
from CurrentTable
join NewTableWhere
on a.UserID = b.UserID
Where a.UserID = 'XXX'

Would produce the following result.

a.UserID a.Company a.Department a.Source b.UserID b.Company b.Department b.Source
User1 fkCompany fkDepartment 1 User1 fkCompany fkDepartment 0
User1 fkCompany fkDepartment 1 User1 fkCompany fkDepartment 0

I am not sure that you need either just to produce a maintenace screen for the new table.

Perhaps you can clarify.

Simi

 

[TysonLPrice]

I should have used User1 in the query instead of 'XXX'

The first select output you listed is what I want. The rows are processed in the application sequentially after the DB call. My question is should I create a junction table between CurrentTable and NewTable. I think not but a co-worker suggested it and my eyes glazed over. I researched junction tables and don't see a need for one. I just wanted to check with the gurus.

Maintenance is not the question right now. Just design.

An example of one in place here now is:

refCompany
pkCompany Description

refDepartment
pkDepartment Description

refCompanyDepartmentJoin
pkComapnyDepartmentJoin fkCompany fkDepartment

A example of using it is:

Select fkDepartment,description
from refCompanyDepartmentJoin
join refDepartment on pkDepartment = fkDepartment
Where refCompanyDepartmentJoin.fkCompany = 1

So again my question is should I create a junction table between CurrentTable and NewTable and if so what should it look like?

Thanks!

 

[simian336]

Don't think you will need one based on your original example because you have both Company and Department in both tables..

Because they are both in both tables you can query out each or join each at any time.

Simi

 

[Crowley16]

I may not have understood your post 100%, so please bear that in mind, however from what I do understand it seems like what you need is basically a copy of your CurrentTable, however with the restriction of unique users removed.

that will be create a junction table between 3 tables: user, company, department.

I would also add in a extra column, fkFunction to reference the actual function as well, otherwise you will not have a primary key.

--------------------
Procrastinate Now!