CODEWITHSUNDEEP
database-design
dpsmails
dpsmails

Reputation: 1

alternative of join in a single db table

I have a situation where a join of 2 tables is being performed. The record structure is like this:

<person>
 <name>person name1</name>
 <dept>dept1</dept>
 <dept>dept2</dept>
 <dept>dept3</dept>
</person>

<person>
 <name>person name2</name>
 <dept>dept4</dept>
 <dept>dept5</dept>
</person>

Here you can see that a person can belong to more than 1 dept, so a table named person alongwith a table named dept will solve the purpose.

Is there a way that I can manage same data in a single table?

I need a way to have a database table where I can have variable number of columns for storing dept values.

Is there a way with RDBMS to achieve this? I would appreciate any out of the box ideas as well.

Thanks in Advance

Upvotes: 0

Views: 114

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43474

You said:

Here you can see that a person can belong to more than 1 dept, so a table named person alongwith a table named dept will solve the purpose.

Think twice that comment. If the relationship between person and dept is owns or visits then is not a one-to-many relationship but rather a many-to-many relationship as a person can own or visit more than one dept and a dept can be owned or visited by more than one person. Given your example, this would translate into this (see dept3):

<person>
 <name>person name1</name>
 <dept>dept1</dept>
 <dept>dept2</dept>
 <dept>dept3</dept>
</person>

<person>
 <name>person name2</name>
 <dept>dept3</dept>
 <dept>dept4</dept>
 <dept>dept5</dept>
</person>

But that, of course, will depend on the requirements, which you haven't mentioned.

Is there a way that I can manage same data in a single table?

I can only think of three ways of doing this (one is more awful than the other):

  • Add columns up to a needed max (assuming you know there won't be more than X departments per person)
  • Leave only two columns, one for person and the other for departments and use it as a multivalued field (yuk!). EG: Use some kind of separator character to identify when a dept finishes and starts the other
  • Entity-attribute-value model: This has been discussed many times in StackOverflow. Here is an exmplanation on how to work with it

I need a way to have a database table where I can have variable number of columns for storing dept values.

What is variable in any RDBMS is not actually the columns but rather the rows so, it will always be complicated to handle this.

Is there a way with RDBMS to achieve this?

Any of the major ones will do.

My opinion? Use more tables! This solutions will bring you compolications and a lot of trouble!

Upvotes: 1

kasavbere
kasavbere

Reputation: 6003

create table mytable(
    record_id int(11) not null auto_increment,
    ss_num   int(9) not null,
    firstname varchar(20) not null,
    lastname  varchar(20) not null,
    middlename varchar(20),
    deptid     int(11),
    deptname   varchar(5),
    primary key(record_id)
)

You may not need all the fields presented. So edit as you need.

Upvotes: 0

Related Questions