How-to store multiple values in one DB column?

Posted on 2007-10-14
Medium Priority
Last Modified: 2012-06-27
Hi! Currently I'm developing simple db catalog application for dvd store in C# using DataSets. I have stumbled upon the following problem. imagine i have two tables in dataset:
MOVIES with columns - id, name, actorId
ACTORS with columns - id, name
Currently i have set ACTORS.id as a parent in relationship with MOVIES.actorId (child). however this way i am able only to specify one actor per movie which is bad since movie usually have more than one actor acting. also note that number of actors per movie is dynamic - it can be 2, 5 or 20 and it would be dumb to create 20 or 50 actor columns in MOVIES table.. so how do i solve this problem?
Question by:latsubs
  • 2
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 20073181
that is a database design issue.
you will need the design to be like this:

MOVIES with columns - id, name
ACTORS with columns - id, name
ACTOR_IN_MOVIE with columns actor_id, movie_id

and voila, you can define which actor is in which movie.
this is called a MANY - to - MANY relationship, and should be implemented like this for best performance and best scalability.

Author Comment

ID: 20073411
thank you. that explains much.

Author Comment

ID: 20076189
however i have one more question regarding table ACTOR_IN_MOVIE - how should i set indexing? should i set as index both fields? or create field id?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20076203
you would usually create 2 indexes. one with both fields, and one with the field you specified second it the other index.
so either 1 index with ACTOR_ID+MOVIE_ID  and the second index with MOVE_ID alone
or 1 index with MOVIE_ID + ACTOR_ID and the second index with ACTOR_ID alone.
the index with the 2 fields should then be defined as primary key, unless:
>or create field id?
you do that: create a dedicated primary key field.
note: you might want to add other fields to this table, for example, a property that specifies if the actor was a main actor, a second role or even on a statist / double etc...  or another property saying if the actor, for that performance, got an oscar or something else ....


Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question