CODEWITHSUNDEEP
mysqldatabasecsvimportmysql-workbench
dctb13
dctb13

Reputation: 31

Importing CSV into MySQL and

I'm very new to using MySQL but have been tasked with creating a database to hold a considerable amount of client data.

The biggest issues I'm having is around normalization and how to import data to ensure it meets normalization rules. Obviously I could put everything into one big table and hope for the best but from anything I've read it will cause considerable problems down the road.

How should I embark on importing data that takes the following form (not actual data but form will be the same just more rows and more columns)

Unique Reg Num |Promotion  |Advert  |Characteristics
---------------|-----------|--------|----------------
123            |20% Off    |Aug 15  |Green;Blue;Purple
124            |10% Off    |Sep 14  |Purple;Red;Green
125            |20% Off    |Aug 15  |Green;Blue;Purple
126            |10% Off    |Sep 14  |Purple;Red;Green

So Promotion and Advert columns should be normalized and have separate tables (this is a many to one relationship I think) linked back to Unique Reg Num however the main issue is how to sort the Characteristics column. (I'll set up separate threads if needed for other questions)

I have several columns like the Characteristics column with delimited data. How should I upload this to a database so that data can be queried using these columns?

I could expand the rows for each delimited piece of data which would give me 3 rows of data for Unique Reg Num 123 (just using the sample table above). However, if I have another column with multiple characteristics, say 3 countries (Germany;France;Italy) then the number of rows trebles to 9 rows of data for Unique Reg Num 123.

I really have no idea how to begin to tackle this issue other than creating a big table.

I'm using MySQL 6.3, with MySQL Workbench. Currently set up in Win 7 for testing but will set actual database up on Windows Server 2012.

If I've left out any relevant info please let me know and I'll update the question.

Thanks in advance

Upvotes: 2

Views: 132

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Here is what your data should ideally look like. I would split your sample table into two tables, one for promotion information and the other for characteristics:

promotions

Unique Reg Num |Promotion  |Advert
---------------|-----------|--------
123            |20% Off    |Aug 15
124            |10% Off    |Sep 14
125            |20% Off    |Aug 15
126            |10% Off    |Sep 14

characteristics

Unique Reg Num |Characteristic
---------------|----------------
123            | Green
123            | Blue
123            | Purple
124            | Purple
124            | Red
124            | Green
125            | Green
125            | Blue
125            | Purple
126            | Purple
126            | Red
126            | Green

These tables are fairly normalized, probably sufficient for your use case. The challenge you face is in getting rid of that CSV data.

Upvotes: 1

Related Questions