Database design: can the same record appear in more than one table?

[G_S]

Hello everybody. I have a question related to database design:

I am trying to make a database for my translation mini-company. I have two entities: translators and proofreaders. The problem is that some of our translators double as proofreaders, that is, they can work in both areas. The question is, is it OK to have the same person appear in different tables? I know that having the same person appear in the same table more than once is troublesome.

Thanks in advance.

[ChrisHunter]

It depends on the layout of you database itself.

If you have a the following tables it's not possible (or isn't ideal):

Person_tbl
* PersonID (PK)
* Name
* JobID (FK)

Job_tbl
* JobID (PK)
* JobTitle

However if you have a the following tables it is probably the best way to go about it:

Person_tbl
* PersonID (PK)
* Name

Job_tbl
* JobID (PK)
* JobTitle

PersonRoles_tbl
* RoleIF (PK)
* PersonID (FK)
* JobID (FK)

This way you can assign multiple people to multiple jobs.

Hope this helps, let me know if you need any clarification.

[deceptikon]

I know that having the same person appear in the same table more than once is troublesome.

Basically you've described the difference between a normalized and denormalized database. A normalized database stores the actual data in a single table and uses referential links (such as primary keys) to share records. A denormalized database duplicates the data as necessary.

There are pros and cons to each design. Normalized databases are cleaner and easier to reason about, but also more complex and slower due to the linking. Denormalized databases are usually designed that way because query performance is critical. For example, Daniweb's database is partially denormalized.

[G_S]

Thank you for your input. I was told that denormalized databases are a mistake, that they are monster to avoid and something professionals never do...

So I should have a persons table and the proofreader and translator are not people but simply jobs they can take? Did I get it right?

[ChrisHunter]

That would mean the more and more jobs there are the more tables you will need. You can limit the size of your DB by just having a Employee table (for the people), a JobTypes table (for the different jobs) and a table named something like EmployeeJobs which references both the Employee table and the JobTypes table.

This way you just add a new record into the JobTypes table for each job (e.g. manager, director etc). Other wise if you keep adding a new table for each your database will become more and more unmanagable.

As for denormalised databases they have their perks and if you're an experienced DBA it's probably as easy to use as a normalised one.

[G_S]

Great. Thanks again everybody. I finally understood this normalized/denormalized table stuff. In fact, I even learned more about tables from these answers than from my teachers'.

Thanks!