FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How do you make a field within field, within a record?





Denvis
I'm using MS access 07 at the moment. I have a table called "TV" and I have field names of ID, TV Show, TV season, TV Episode and also a another field called episode seen.

What I wanna do is I wanna make the fields of TV season and episode within the TV show. That way I don't need to have such a messy database. Kind of like...

Quote:
Field Name
ID
TV
TV Season (this is within TV field)
Season [1,2,3,4,5,6,7,8,9] (This is within TV season)
[Have I seen? Yes/no]
TV Episode (within TV season)
Episode [1,2,3,4,5,6,7,8,9]
[Have I seen? Yes/no]


What would I need to do to achieve that result?
badai
why don't you create another 2 tables: TV season and TV show, use ID from TV as foreign key.
Denvis
badai wrote:
why don't you create another 2 tables: TV season and TV show, use ID from TV as foreign key.


Because I've done it this way 2 years ago and I wanna do it again.
Fire Boar
If the number of seasons and episodes are fixed, you can do it by simply adding fields (episode1, episode2, ..., episodeN). Otherwise, you need to use a foreign key - there are no two ways about it.

See here for more information.
Denvis
Oh, I got it. I made two tables and related them to each other. God i'm an idiot. So can you explain normalization? I'm studying it in IPT (information processing design) at school. They say it's the process of organizing data into tables so that the results of using the database are unambiguous and as intended. I seriously need a better text book... I don't completely understand it. I also keep hearing about 1NF, 2NF, 3NF. It means First, second, third normal form right? But what's the difference of them all and what is the purpose of them? Man this text book doesn't say bugger all.

There's a section in my database where I'm in table view of TV Season and I have a field named "Season Viewed" with a data type of boolean. When I click the little cross box thing (there's probably a proper term for it) It opens up and within it has the table, Seasons episode. In that season's episode it also has a field called "Episode seen" with the same data type. Is there a possible way for me to make it that, if I have seen that Season, indicated by checking the "Season Viewed" box, the" Season Episode viewed" box will all change from a false output to a true? If so, how?

I also wanted to ask what it meant exactly by one to one, one to many, many to many. Right now, I have a one to many and I know that means, one record in my first entity (Season_ID, Season) is related to many records in the second entity (Season_ID, Episode). Is it by having a season_ID in both tables make it one to many or am I totally wrong? If so, can you explain it? Also, how would I be able to produce a relation that is one to one or many to many?
Denvis
Okay, so I have another problem. I've modified my DB atm to look like this,

TV Shows
Show_ID
Show_Title
Show_Rating
Show_Seen

TV Season
Season_ID
Season_Name
Season_Rating
Season_Seen
Show_ID

TV Episode
Episode_ID
Episode_Name
Episode_Rating
Episode_Seen
Season_ID

Shows_ID (Shows) is related to Show_ID (TV Season), Season_ID (TV Season) is related to Season_ID (TV Episode). I'm adding in entries into my DB, only using the TV Shows Table cause it's a lot easier than separately adding each one through each table. So the problem is in my TV Episode. It's not in place. Say for example, I have...

Show = HIMYM (Show ID = 1)
HIMYM Season 1 (Season ID = 1)
Episode 1 of season 1(Episode ID = 1)
Episode 2 of season 1(Episode ID = 2)
Episode 3 of season 1(Episode ID = 3)

But when I add another entry...

Show = NCIS (Show ID = 2) < - - All Good
NCIS Season 1 (Season ID = 2) < - - All Good
Episode 1 of season 1 (Episode ID = 4) < - - HUH?! WHY ISN'T IT Episode ID = 1?!
Episode 2 of season 1 (Episode ID = 5)?!?!?!?!
Episode 3 of season 1 (Episode ID = 6)?!!??!

How do I fix that?!
Is there something wrong with my relationships?
Do I need to add more relationships?
Fire Boar
The table structure looks good. As for your problem, you seem to have an autoincrement set on episode ID, which makes it automatically take the next value up from before. If you don't want this behaviour, you should set a composite primary key. So on TV Season, your primary key would be (Season_ID, Show_ID) and on TV Episode, your primary key would be (Episode_ID, Season_ID).

A complete SQL definition of your tables would look something like the following:

Code:
CREATE TABLE tv_shows (
  show_id INT PRIMARY KEY,
  show_title VARCHAR(255),
  show_rating INT,
  show_seen BOOLEAN
);
CREATE TABLE tv_season (
  season_id INT,
  season_name VARCHAR(255),
  season_rating INT,
  season_seen BOOLEAN,
  show_id INT,
  PRIMARY KEY (season_id, show_id),
  FOREIGN KEY (show_id) REFERENCES tv_shows(show_id) ON DELETE CASCADE
);
CREATE TABLE tv_episode (
  episode_id INT,
  episode_name VARCHAR(255),
  episode_rating INT,
  episode_seen BOOLEAN,
  season_id INT,
  PRIMARY KEY (episode_id, season_id),
  FOREIGN KEY (season_id) REFERENCES tv_season(season_id) ON DELETE CASCADE
);



Now, you mentioned confusion with normalization. As it happens, your current structure is in 3rd normal form. I'll give you a quick summary of each.

1st Normal Form (1NF): No repeating groups. That is, each field can only take one distinct value. This includes using multiple fields, some of which might be optional.

The following are not in 1st normal form:
(Name, Telephone Numbers) - The "telephone numbers" field is repeating.
(Name, Tel. 1, Tel. 2, Tel. 3) - This uses optional fields to get around the restriction.

For 1st normal form, the following design works:
(Name, Telephone Number) - Each name may be listed more than once.


2nd Normal Form (2NF): The database must be in 1NF, and additionally every field needs to depend on all of the table's primary keys.

Let's extend the example above to add a Date of Birth field.

(Name, Telephone Number, Date of Birth)

This is in 1st Normal Form, but if someone has more than one telephone number, their Date of Birth and name are listed twice. The primary key would is (Name, Telephone Number), but Date of Birth does not depend on Telephone Number. For 2NF, this solution works.

(Name, Date of Birth) (Name, Telephone Number)

This causes each person to be listed exactly once in the first table, and 0 or more times in the telephone number table. This way we have also solved another problem: if someone had no telephone numbers registered, in the 1NF design we would lose all information about them. In the 2NF design, this doesn't happen.


3rd Normal Form (3NF): The database must be in 2NF, and no field may depend on any other non-key field.

Here's an example:

(Job, Month, Employee name, Employee DoB)

This is in 2NF, but not 3NF. The reason being, Employee DoB and Employee Name depend on each other. This becomes intuitive when you start putting in data - suppose someone did one job in January, but a different job in February. Suddenly they are listed twice in the table. To put this in 3NF, something like the following database structure is required:

(Job, Month, Employee name) (Employee name, Employee DoB)


Actually, in the real world integers are usually used as primary keys, like what you are doing with your ID fields. This is not always the case, and very rarely the case in school.
Denvis
Fire Boar wrote:
The table structure looks good. As for your problem, you seem to have an autoincrement set on episode ID, which makes it automatically take the next value up from before. If you don't want this behaviour, you should set a composite primary key. So on TV Season, your primary key would be (Season_ID, Show_ID) and on TV Episode, your primary key would be (Episode_ID, Season_ID).

A complete SQL definition of your tables would look something like the following:

Code:
CREATE TABLE tv_shows (
  show_id INT PRIMARY KEY,
  show_title VARCHAR(255),
  show_rating INT,
  show_seen BOOLEAN
);
CREATE TABLE tv_season (
  season_id INT,
  season_name VARCHAR(255),
  season_rating INT,
  season_seen BOOLEAN,
  show_id INT,
  PRIMARY KEY (season_id, show_id),
  FOREIGN KEY (show_id) REFERENCES tv_shows(show_id) ON DELETE CASCADE
);
CREATE TABLE tv_episode (
  episode_id INT,
  episode_name VARCHAR(255),
  episode_rating INT,
  episode_seen BOOLEAN,
  season_id INT,
  PRIMARY KEY (episode_id, season_id),
  FOREIGN KEY (season_id) REFERENCES tv_season(season_id) ON DELETE CASCADE
);



That's exactly what I was thinking. But how would I be able to do that without typing up a SQL definition and just using the provided GUI by access 2007?

Sadly, at school we learn the basics of a lot of many things. I really hate that, the school system in Australia sucks. I don't understand why can't we learn a couple things and learn it in depth. Whatever, i'm still first in my course Cool
Fire Boar
I've never used Access 2007 - I avoid Microsoft Office on principle and GUI database software in general. I did once have the misfortune of doing a course which required MS Access 2003. In that, I believe you can just hold Ctrl and click on multiple fields, then set the primary key that way. To set up the relations, I think there's a "relations view" where you can join fields as one-to-many relationships.

Here, let me Google that for you.
Related topics
Argentina tops Brazil for World Cup spot
Inserting data to MYSQL with a PHP script
science vs. religion
Women and Rock Music
Intelligent Design?Or Evolution?
STEELERS
Mass and Energy
Can you be psychic?
How does the sid field in nuke_stories table work in PHPNuke
Uploading Files to the Database
Strange Problem in PHP-MYSQL
Newton's Electric Clockwork Solar System
Disable "Enter" Key in Form script
align text within input field with text outside
Reply to topic    Frihost Forum Index -> Computers -> Computer Problems and Support

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.