I'm creating a database that looks similar to this

However, someone has told me that I have poor database design because inside my movie table "CompleteCast" I'm using delimited fields for my actors.

I've already got separate tables for both "actor" and "movies" so why is this a bad thing and what can I do to change this problem?

it's a bad thing because it violates first normal form

why is 1NF important? because without it, your queries will be horrendously complex (e.g. to find all the movies a given actor is in) and slow as cold glue (because they will require table scans)

There's nothing fundamentally wrong with storing delimited strings in a column but in this case it's unlikely to be very practical.

For example: How will you ensure that the actor names in the cast column correspond to those in your Actor table? It's very difficult to enforce such a rule in the database. How will you locate and update every reference to an actor's name if it appears in lots of delimited strings (for example if an actor's name changes or needs correcting)? Finally, and most obviously, how do you expect to answer such queries as "Show me all the films in which Marlon Brando appeared"? It's very hard to do that efficiently if you first have to parse a string on every single row.

The solution is to create a new table that joins actors to movies, probably with just two attributes (like: ActorMovie {Actor, Movie} where {Actor, Movie} is also the key of the table). This is often called a "joining table" or "association table" although personally I don't find that terminology very useful.

Do you consider delimited strings atomic then David?

pootle flump ur codings are working excelent.

okay, i'll bite -- what do you call it?

A string is a string. It's only a single value by definition. The question of whether the same information could be better stored in other ways depends a lot on the circumstances and how the data is used. Although it can be an important design issue I don't believe it's desirable to make a single universal rule about it.

1NF isn't strictly relevant here because most popular DBMSs (SQL ones) don't allow you to store more than one value in a single column anyway.

The thing is, I'm unsure exactly when a table qualifies as a "association table" anyway. It seems to me that an association table can be any table with more than one foreign key. If that's so then I don't really see why I need a special name for those tables. Having more than one foreign key isn't so special is it?

but not what it represents. XML is a string. A bitmask is an integer. But what they represent is more than a single "thing".

I'd add that my internal-used-only-by-myself definition is that an association table has no non-key attributes. As such, it represents an association only and not a distinct and tangible entity.

pootle flump ur codings are working excelent.

In my mind, an association table can also contain a date_begin and a date_end, since many associations are time delimeted.

-PatP

I'd agree there. Associations can easily gather attributes, and become entities in themselves. This kind of falls in line with my "There's no such thing as a lookup table" mantra.