Multiple items in a single column SQL

Question

I'm creating a charter database structure. I'm trying to figure something out, I have read on here that databases should be normalized. Now, I'm a bit confused on this because I believe I have stumbled across database tables that have multiple items in a single column. What I mean by this is, let's say the db creator is creating a column, the column name is IP Address. Now within that column there's a list of ip addresses separated by semi-colons, is this the correct way to do this?

My scenario is this, I'm creating a list of items, each item is going to be a list of "amenities". Should I create a separate table and link it? Or should I be just having the list of amenities separated by a delimiter?

Answer 1

If you see someone putting different "things" into a single database table using a delimiter to keep them separate, that's a problem.

It could be that someone does not have permission to change the database structure or that they just don't "get" databases. Either way, it's a performance and maintenance headache.

---

In your situation I would probably create 3 tables: 'Item', 'Amenity', and 'ItemAmenity'.

The 'Item' table has records that represent the thing that has amenities. The 'Amenity' table has records that represent each possible amenity. The 'ItemAmenity' table has foreign keys that link a particular 'Item' to a particular 'Amenity'.

This is called a 'many to many' relationship.

You can find some example code in MySQL many-to-many relationship with FOREIGN KEYS

Answer 2

Always use a separate linked table for storing “multiple x for each y” type data.

First normal form requires that each field in a row contain an atomic value – that is, a value which is not divisible into multiple sub-values.

In case you’re wondering just how useful a separate table will be, compared to a comma-separated list in a single column, consider how you’d go about writing these queries:

• A list of entities with more than 4 but fewer than 9 amenities.
• A list of entities associated with “amenity A” but not “amenity B” or “amenity C”.
• A list of entities with the same amenities as “entity X” and “entity Y” combined.

With a separate table, the above queries would be trivial with basic SQL.

Answer 3

If you want flexibility and some ease in managing the data, you'll create a separate table and link them, but if you have minimal requirements and performance is a concern, you can put them all into one.

The other developer may have decided there was no need to select specific IPaddresses or get a count of the number of IP Addresses each entity has, so why bother with another table? In fact, querying the data and having to write code to "bundle" all those IP addresses in one field may not have been ideal.

The easy answer in a relational database is to normalize. Your data entry and management will be more flexible. Your use case will tell you whether or not this is necessary. Deciding to change later on is always more difficult. That's the risk you take if you get to concerned with performance. There are plenty of things that can be done to speed things up without sacrificing normalization.