File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes Multiple tables or single table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Multiple tables or single table" Watch "Multiple tables or single table" New topic

Multiple tables or single table

Suhem Programmer

Joined: Feb 13, 2007
Posts: 21
Hi All,

Can someone suggest me please whether to create multiple tables or it is ok if we create just one table.

I have a situation where I am storing persons' data in multiple tables.I was just wondering can I use one table instead to store the information about a person?
Craig Collins

Joined: Mar 30, 2008
Posts: 8
Assuming you are storing data in a relational database then the concept of normalisation must come in to play here ...

if storing all person data in one table will mean there will be redundant data then this should be avoided.

classic example is Employees and Departments;

an Employee may work in only one Department
but a Department may employ many Employees

so we make one table for Employees and one table for Departments - otherwise for employees working in the same department we will end up storing the department name many times (redundant data).

more difficult to maintain data integrity in this case - when the dept name changes need to find all occurences of the dept name - dept name may end up coded slightly differently for different emps (IT, I.T, it, ...).

Depends on your data - if data in the tables is related in a one-to-one relationship then could store in one table.

Hope this helps.
Suhem Programmer

Joined: Feb 13, 2007
Posts: 21
Hi Craig,

Thanks a lot for the reply. But all the tables have one to one relationship. So is that fine if I put them all together in one table?

I am sorry about not mentioning it before, I am using relational detabase(oracle 10g)

Craig Collins

Joined: Mar 30, 2008
Posts: 8
Hi there, perhaps you could describe the data in the tables, i.e. what entities the tables represent, and the attributes in each table.

we have an Employee table presumably with Name, Date of Birth ...
but what are the other tables?
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
I would still do it as a normalized table as suggested.
Currently you have a 1 to 1, but users like to change things.
It will make modification easier in the future if you use normalized tables.
I agree. Here's the link:
subject: Multiple tables or single table
It's not a secret anymore!