-2

I have a database with the table End_user with columns Name, City, Country, Plant_name, Unit_ID and Project_NR.

Now it is possible that an End_user is in multiple cities or countries with multiple Plants, units and Project_nr's. So the database should be able to have multiple values in these cells.

I tried multiple query's but I do keep getting errors.

Example:

  • Name: Petrol
  • City: Amsterdam, Reykjavik
  • Country: The Netherlands, Reykjavik
  • Plant_name: Plant_1, Plant_2, Plant_3
  • Unit_ID: 123, 345
  • Project_NR: 1234, 2123, 3456, 7856

Can someone help me with the query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nick van der Deijl
  • 23
  • 4
  • 1
    And - grab a book about relational databases. Theory. Then redesign your approach. Multiple values per field = programmer who has no clue how to design a relational 1:m model. Not one table, dude. Multiple. – TomTom Nov 11 '14 at 14:14
  • I swear this site needs a `Non-1NF` tag. Try looking at [this](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows). – Bacon Bits Nov 11 '14 at 14:16
  • It's a **fundamental** concept of relational database design to have **at most one value** in every column or every row. Do **NOT** put multiple values into a single cell - that's guaranteed to come back and bite you and hurt your performance and your ability to develop your software! To handle this situation, you need to use **relationships** between tables - one `end_user` can be linked to multiple `city` and `country` rows. That's how relational databases work. – marc_s Nov 11 '14 at 14:33

1 Answers1

1

It is bad practice to store multiple values in a single field on a row. Without going into great detail about how to structure you should consider the question that you are trying to answer. So I'm assuming that your main table has multiple records for each end user if they are based in more than 1 city or country? I think you want a table where you want 1 record per end user. So what is the aim of this table? Maybe consider doing counts of things like cities e.g. this end user has 2 cities. If you need the detail on this then switch back to your table with 2 records per end user. Or you could create fields that are flags e.g. a field for London and one for New York where you can set the flag to be 1 or 0. This would only work in a very limited way but might work for your dataset.

user3302483
  • 845
  • 4
  • 12
  • 20