Multiple tables or single table

[Suhem Programmer]

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]

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).

Emp   Dept
----- ------
John  Accounts
Kath  IT
Bill  Accounts
...   ...

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]

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)

Thanks,
Hemali

[Craig Collins]

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]

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.