How-to store multiple values in one DB column?

Posted on 2007-10-14
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 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
    LVL 142

    Accepted Solution

    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

    thank you. that explains much.

    Author Comment

    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 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now