latsubs
asked on
How-to store multiple values in one DB column?
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?
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?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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 ....
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 ....
ASKER