Hey guys (and probably girls). I'm modelling a huge database in mysql for a company that i work and i'm stucked on things that you might help me.
My question is very simple: How do i know that a number of foreign keys is enought ?
I have 8 tables that describes the following datas:
1st relationship
- table country (pais)
- table state (estado)
- table city (cidade)
(city => state => country)
2nd relationship
- table department (departamento)
- table sector (setor)
- table role (cargo)
(role => sector => department)
3rd relationship
- legal entity (entidade juridica)
- company (empresa)
(company => legal entity)
And finally i have a ninth table called employee and this table have foreign key references of all the tables previously described.
- (primary) id-colab
- fone_colaborador
- fax_colaborador
- ativo_colaborador
- email_colaborador
- (primary) cargo_id_cargo (cargo table)
- (primary) cargo_setor_id_setor ( cargo table)
- (primary) cargo_segor_departamento_id_departamento (cargo table)
- (primary) empresa_id_empresa (empresa table)
- (primary) empresa_entidade_juridica_id_entidade_juridica (empresa table)
(primary) empresa_entidade_juridica_cidade_cidade_id (empresa table)
(fK) cargo table
- (fk) empresa table
The main concept of this model is.
- I have a register of a legal entity.
legal entity must be placed on country => state => city
I have a register of a company
- Company should inform whether it is a branch or parent company
Company should have a referece to the legal entity table (that owns address, phone, state id, city id, country id, postal code and etc...)
I have a register of a role
- Role should have a reference to the sector table
- sector table should have a reference to the department table
And finally, the main table called employee described on the image above.
I hope you guys have undertood me !
See ya.
