0

I have a table named buildings each building has zero - n images

I have two solutions

the first one (the classic solution) using two tables:

buildings(id, name, address)
building_images(id, building_id, image_url)

and the second solution using olny one table

buildings(id, name, address, image_urls_csv)

Given I won't need to search by image URL obviously, I think the second solution (using image_urls_csv column) is easier to use, and no need to create another table just to keep the images, also I will avoid the hassle of multiple queries or joining.

the question is, if I don't really want to filter, search or group by the filed value, can I just make it CSV?

ssilas777
  • 9,672
  • 4
  • 45
  • 68
ahmed
  • 13
  • 5
  • 3
    CSV type values are the antithesis of a proper relational database. If you want those values related in any form whatsoever you want a relational table. This is a fundamental of the [Zero, One or Infinity Rule](http://en.wikipedia.org/wiki/Zero_one_infinity_rule) as applied to [database normalization](http://en.wikipedia.org/wiki/Database_normalization). – tadman Apr 02 '18 at 19:24
  • 1
    NOT GOOD, wait for SQL expert answers and explanations! – ssilas777 Apr 02 '18 at 19:25
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Timovski Apr 02 '18 at 19:40

2 Answers2

1

On the one hand, by simply having a column of image_urls_list avoids joins or multiple queries, yes. A single round-trip to the db is always a plus.

On the other hand, you then have a string of urls that you need to parse. What happens when a URL has a comma in it? Oh, I know, you quote it. But now you need a parser that is beyond a simple naive split on commas. And then, three months from now, someone will ask you which buildings share a given image, and you'll go through contortions to handle quotes, not-quotes, and entries that are at the beginning or end of the string (and thus don't have commas on either side). You'll start writing some SQL to handle all this and then say to heck with it all and push it up to your higher-level language to parse each entry and tell if a given image is in there, and find that this is slow, although you'll realise that you can at least look for %<url>% to limit it, ... and now you've spent more time trying to hack around your performance improvement of putting everything into a single entry than you saved by avoiding joins.

A year later, someone will give you a building with so many URLs that it overflows the text limit you put in for that field, breaking the whole thing. Or add some extra fields to each for extra metadata ("last updated", "expires", ...).

So, yes, you absolutely can put in a list of URLs here. And if this is postgres or any other db that has arrays as a first-class field type, that may be okay. But do yourself a favour, and keep them separate. It's a moderate amount of up-front pain, and the long-term gain is probably going to make you very happy you did.

Tanktalus
  • 21,664
  • 5
  • 41
  • 68
0

Not

"Given I won't need to search by image URL obviously" is an assumption that you cannot make about a database. Even if you never do end up searching by url, you might add other attributes of building images, such as titles, alt tags, width, height, etc, so you would end up having to serialize all this data in that one column, and then you would not be able to index any of it. Plus, if you serialize it with one language, then you or whoever comes after you using a different language will either have to install some 3rd party library to deserialize your stuff or write their own deserialization function.

The only case that I can think of where you should keep serialized data in a database is when you inherit old software that you don't have time to fix yet.

kloddant
  • 1,026
  • 12
  • 19