Re: Multiple entries in a field

On Tue, 08 Aug 2000 22:33:29 GMT, Rohit <rohitd_at_flashmail.com> wrote:
>
>[...] Eg. for a
>database called TEAMS,I want fields such as: Name,Captain,Players,Year
>Founded,etc.Now obviously the last one has to have the names for *each* of the
>team,so it can't be done with just a VARCHAR(50).There will be a separate
>database for each of the team,with things such as their Name,Age,Year of
>joining,etc.... or should there be?

If you mean with a 'separate database' a 'seperate table within the same database' then, yes, there should be.

>How exactly would I go about doing this?

Roughly it works like this: For all the data items that you want to store you have to ask yourself the following questions:

1. Does it describe the property of a thing and of what thing? E.g. the player name is a property of a a player and the founding date is a property of a team.
2. Does it describe the property of a relationship between things and of what relationship? E.g. the year-of-joining is a property of the plays-for relationionship between player and team.

Then, for every type of thing and every type of relationship that you have found you create a separate table that describes all the properties of this thing or relationship. In your example this would be tables PLAYER(Name, Age), PLAYES-FOR(Year-of-joining) and TEAM(Name, Founding-year).

After this you have to make sure that the following holds (or extend the tables such that it holds):

1. Every table that describes things describes enough properties to identify every separte thing.
2. Every table that describes relationships describes enough properties of the things that the relationship holds between to identify these things.

The first requirement is rarely a problem. The second one usually means that you have to extend these tables with some of the properties in the thing-tables. In the example the PLAYER table and TEAM tabel are Ok bacause their names will be enought to identify each of them. In the PLAYES-FOR table, however, we have to add columns for identifying a player and a team. To do this we add the columns Player-name and Team-name. The first refers to the name column in the PLAYER table and the second refers to the Name column in the TEAM table. The resulting database structure now looks like this:

PLAYER(Name, Age)

PLAYES-FOR(Player-name, Team-name, Year-of-joining)

TEAM(Name, Founding-year)

This will give you a first approximation of the basic structure of your database schema. After this you can make some optimizations by merging some tables into one but this should only be done if you are absolutely sure that it will not create any problems such as the ones you already noted.

One example of this is when you have a table that represents a relationship between two things and is a so-called one-to-many relationship. A relationship between two types of things is a one-to-many relationship if it associates a thing of the first type with possibly more than one thing of the other type but every thing of the other type is associated with only one thing of the first type. If the reverse holds that the relationship is said to be a many-to-one relationship. An example of a many-to-one relationship is the playes-for relationship that associates every team with several players but every player with only one team.

So, if you have a table that represents such a one-to-many (or many-to-one) relationship than you can safely merge this table into the table that represents the things on the many-side of the relationship. So the table for playes-for may be merged with the table for player. The resulting database structure then looks like this:

PLAYER(Name, Age, Team-name, Year-of-joining)

TEAM(Name, Fouding-year)

Note that I removed the Player-name column in the PLAYER table because it would be a duplication of the Name column.

This completes the design of the basic structure of your database schema. Note that you still need to think about a lot of other things such as the domains of the columns, the keys, the foreign keys and other rules that should hold for the data in your database.

>I
>should be able to query the first db for all teams founded after a certain
>year,for example,and then get whatever data on each member of that team.

You shouldn't worry about this. Any decent database will allow you to recombine the data you put in separate tables. Well, provided you designed your tables correctly. :-)

Hope this helped,

• Jan Hidders