How to normalize this database?

Asked

Viewed 3,708 times

3

I’m having a problem creating a database where a company can have up to 16 workers. Where each worker can have this data:

$sqlupdate3 = "Update tb_detalhe_trabalhador SET
Nome3='$Nome3',Funcao3='$Funcao3',
MedicaValidade3='$MedicaValidade3',
MedicaAnexo3='$MedicaAnexo3',
EPISValidade3='$EPISValidade3',
ProjectistaNumero3='$ProjectistaNumero3',
ProjectistaValidade3='$ProjectistaValidade3',
GasNumero3='$GasNumero3',
GasValidade3='$GasValidade3',
RedesNumero3='$RedesNumero3',
RedesValidade3='$RedesValidade3',
SoldadorNumero3='$SoldadorNumero3',
SoldadorValidade3='$SoldadorValidade3',
MecanicoNumero3='$MecanicoNumero3',
MecanicoValidade3='$MecanicoValidade3',
ClasSoldadorNumero3='$ClasSoldadorNumero3', 
ClasSoldadorValidade3='$ClasSoldadorValidade3'
  where id=$id ";

 mysql_query($sqlupdate3) or die(mysql_error());

It will be better to create a table enterprise and 16 laborer or just a laborer?

mysql

  • @Bacco I would not mark as duplicate because this case is specific, while the other question is generalist.

  • @Andrey remember that you can vote to reopen if you want.

  • Chris, search for normalization: http://www.luis.blog.br/normalizaca-de-dataos-e-as-formas-normais.aspx and http://pt.wikipedia.org/wiki/Normaliza%C3%A7%C3%A3o_de_dados

  • Chris, I’ll give you a hint: The title of your question is not appropriate. Change to something like "How to normalize this database?" I would also put the CREATE TABLE in SQL instead of the UPDATE in PHP in question.

  • 1

    Take a look at this later, there is an example of use here: http://sqlfiddle.com/#! 2/98bf7/2 . - In the example there is more than one company, and a select that shows how to take from one, and has a JOIN to merge the company and employee data in a select only after.

2 answers

6

You can normalize by following the standard of the 5 Normal Forms (Fns) given this example below a rental company for being simpler and presenting fewer tables after being standardized: 1FN

2FN

3FN

4FN

I think that the proposal of this site is to clarify or help in the solution of the problem and not to make completely a given problem presenting its entire solution. The development of the problem starts from your person from the understanding of the problem.

  • You could still add a table of Movie Categories, this Database is not normalized.

  • This yes normalized.. what you can do is just an improvement to decrease data/value redundancy by repeating the 4F and 5F rules. Because the 1F,2F 3F rules are the ones that really determine database normalization.

0

The ideal would be to separate everything possible, for example up to the functions of the employees, there should be a table called functions and there be registered and then linked by ID to the employees. The point is that there are few employees which would result in making only one table to generalize, but it is as I think: Today there are 16 tomorrow may be 20, 30, 100...

  • ok, but being only necessary 16 tables how can I relate the 16 tables companies? And as I can for example in some case that the company has 4 workers as I can tell the comic that the tables 5,6,7,8,9 and so on that is empty?

  • @Chrisadler I reread almost all your questions and answers, and I still don’t understand one thing. What’s wrong with having ONE "workers" table, and putting the records in this table? But not in the same line as you do, but in separate lines.

  • Because have a very extensive table and are giving error by the size of fields. I wanted a solution to solve this my problem

  • 1

    @Chrisadler a table with 16 rows would not be extensive. It’s getting extensive because you’re putting more than one worker on the same line, just that. You would need a table with A name, A function only, etc, and in this table would make several INSERTS, one for each worker.

  • @Chrisadler In line 1 you would have the name of the first worker, in line 2 the name of the second worker, etc. The way you are doing, you put the name1 and Nome2 in the same line. There is no table that fits there. Tables usually have columns and rows (synonym of fields and records). You are creating fields where you should create records.

  • And then how can I relate table 1 table 2 and table 3 to tb_company?

  • 2

    @Chrisadler inserting the company ID in each of the employee lines. In your own way you are wasting a lot of time to solve a simple thing, by insistence. Anyway, I wish you good luck. But keep trying, maybe someone responds the way you want, there are some nice people on the site. What I say is, with 16 tables, it gets complicated, and with everything in one line, it gets complicated. If you have a company-only table, and only one for workers, with several lines, the ID solves.

  • This is where I wanted to get to.. Now imagine that there are only 2 workers. the other tables have correct empty id? when filling in the next company, you have the id of the previous company

  • Let’s go continue this discussão in chat.

Show 4 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.