For flat data which comes in with fields like:








CSV files are regularly received, with the above as typical columns. Some fields, such as "class", should definitely have a lookup table because there's repeating data.

If I were to create this from scratch I would have a "class" table so that the table of items has a many to many relationship as:


It's a bit painstaking to go through and create relationships across so many fields. Definitely I want to get the data from CSV and into a database, just having trouble visualizing the overall structure so that it's relational and "not flat".

I'd like to get the data to 3NF incrementally. Vague question, yes. Looking for a starting point on how to approach this problem even with pencil and paper.


You wrote

If I were to create this from scratch I would have a "class" table so that the table of items has a many to many relationship

This implies that one item can be in many classes. However, your example CSV column list shows a class column right next to all the other item columns. This means each row in your CSV will represent only 1 relationship between an item and a class.

Therefore, if your data truly do have a many-to-many relationship between item and class, the only way I can see a CSV modelling this with the structure you gave, is for an item to be repeated on multiple rows - and each row specifies a different class.

If this conclusion is true, then realistically, you do need to model a many-to-many relationship using that intermediary table ... at least for your class information.

How your import will recognise that subsequent rows repeat the same items, and correctly insert the item once, the class once, and the intermediary joining table rows (several rows per item) - is a different question. However your intention to model as many-to-many using a middle table, is sound.

However, if your data (always) has only 1 item per row, then I struggle to see how the CSV can encapsulate information about a many-to-many relationship with class, as you describe. If that's the case, then you don't need the many-to-many middle table - just create your class table and reference it via a foreign key relationship on your item table.

Apart from the above specifics relating to the sample data structures you gave, the process of normalising data, and choosing which decisions to make, would probably require you to read through a few tutorials on the process. I won't link to any specific one, but there will be plenty for you to find.

Hope the above helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.