Normal Forms Data Modeling

Asked

Viewed 185 times

-1

inserir a descrição da imagem aquiI have a file excell with PRODUCT AND ORDER information. It is out of the normal forms as the information is all in one table.

I’m climbing up through bulk insert this information for a database in Sqlserver. The first table in the image shows how it is currently. The other two should look like after the normalization. How to proceed to associate the new field created in the products table (Product code) with the request? Since in the first denormalized table there is no information for this field?

modeling normalization

  • You are creating the Products table or will use an existing one to effect the relationship?

  • I am creating the Product table but based on the information I already have. So in this information I do not have a product code.

  • Do the bulk insert in the Products table only with the description and then add the primary key.

  • Ok, that’s easy. The problem is: in excel table I have only one row. Ex:

  • You want to include the new products in an existing table and with the primary key definite?

  • This is easy to do, it happens that from the moment I separate product information like color, name, size. She’ll get lost and I won’t know which product she belongs to anymore. Na tabela excel que tenho esta assim: NomeFulano, CodigoFulano, EndereçoFulano, DataPedido, NumeroPedido, NomeProduto, CorProduto, tamanhoProduto. Note that I have the Codefulano field, I can use as pk in the Client table, and I also have Numeropedido, but I don’t have Codeproduct.

  • You can resolve this manually by registering 1 to 1 relationships if you don’t have a lot of data. For example ,if you only have about 100 records, do it by hand because the time cost will be equal to or less than creating a script to automate data migration. If it’s more than 400, 1000, etc., it might be more feasible to create an automation. In the case of an automation, a simpler logic is to set the field of the product code according to its name. But you should also observe the field "corProduct".

Show 2 more comments

1 answer

1

Suggestion of how to proceed:

  1. Do the bulk insert of the entire excel file, including with the Product Description for a bd table.
  2. Create the Product table with product descriptions and then add to primary key.
  3. Create the Product Code field in the table with the product description and the other fields.
  4. Update the Product Code field using the Product Description two tables to make the association (join).
  5. Delete the product description field from the table and only leave it in the table product.

I know it’s not good practice to do join with string fields, but, as it is only with palliative, I think will have no problems.

  • @Felipe Gomes Put the images in the post.

  • I put the pictures in the post now. Sorry I’m still Noob on the forum.

Browser other questions tagged modeling normalization

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