DVD Collection Database

[MNNovice]

Hi:

I need some help with the ID numbering for a database I am designing for my DVD collection.

I have some general idea of what I would like to accomplish. Here it is in a nutshell.

ENG000.FE000.S.0000

The first three letters identifies the language followed by three digits that identifies the serial number for that DVD within that language.

Next two letters designates the category (FE for feature film, DO for Documentary etc.) followed by three digits for the serial number within that category in that language.

The next letter designates it to be: S for Single, D for double, M for multiple (more on this later)

The last four digits is the running number in the entire database.

I would like a DVDID field to be automatically populate this info once I enter the Languge, Category, Single/double etc.

Now what I am struggling is with the S / D & M. Some of my DVDs have more than one title. For example, a DVD may contain three different titles of documentary films. Now I would like to assign one ID for this DVD but I would like to list all three or four titles under one single ID?

Should I not need multiple fields for titles? Title1, Title2, Title3? This way I should be able to generate a report that will list all possible titles under one ID number. Am I making sense? Is it possible?

Any help from anyone will be greatly appreciated. I know I will have to provide a detailed data.

Many thanks.

[mshmyob]

You need to normalise the database. If you put in multiple titles in a table you have what is known as repeating columns. Identify your business rules such as EACH DVD may have MANY titles. Then decide if EACH title can be on MANY DVD's or is it EACH title can be on ONE DVD. Tell us and we can tell you the table structure.

You should also read about normalization Database Normalization and Table Structures - bytes.

cheers,

[MNNovice]

A DVD may have many titles and yes, a title can be on different DVD.

For example: DVD #1 may be one documentary DVD #2 may have more than one documentary with a title that's common in both the DVDs (1 & 2).

Am I explaining my problem? Thanks.





@mshmyob

[ChipR]

First, identify the primary key to identify a single dvd. Maybe you want to use this 4 digit number and let it autonumber, but I'll call it ID.
Make a table with fields
ID, Language, LSerial, Category, CSerial
or something similar.

Then a table with fields
ID, Title
where there is no primary key because there may be multiple records per ID. Make the ID an index though for faster operation.

Then your data entry is aligned with the table, and you can easily do a query and Join the tables on the ID to get all the data together. The query can also have a field composed of the other fields with "." between them if that's how you want to see it.

Do read about normalization though.
Good luck!

[NeoPa]

This is very similar to what was discussed for the CD database M.

Do you have what you need for this now?