Best practice: database referencing tables -

in database design feelings of tuple vs referencing table small pieces of data?

for instance, supposing designing schema involving office management. want record department each employee belongs to, otherwise uninterested in information relating departments. have department string/char/varchar/etc in employee table, or have instead foreign key, relating department table.

if department table recording nothing other department names, 1 want combine employee table. if contained in employee table cannot guarantee users call hr "humanresourses", may call "h-r", may call "human resources", etc. having foreign key guarantees can 1 thing. also, if other information ever added departments, easy if in table of own.

so people think it? naturally more tables , referencing have negative impact on performance. question asked oracle 11g in mind, doubt type of rdms involved has bearing on design consideration.

if use related table, don't have performance problem of updating 1,000,000 records because personnel department became human resources department.

you have option. create table , use lookup data entry. store information in main table.

however, prefer option of using related table departments , storing id department , employee in join table has ids , start , endates. on time employees tend move 1 department another. helpful reporting able tell department in when. need consider how data used on time , in reporting when designing sort of thing. short-sighted designs hard fix later.

your concern having many tables unfounded. databases designed have many tables , use joins. if index correctly, there not preformance implications databases. , know what,i know of realtional database many many tables have terrabytes of data perform fine.