On 15 Aug, 16:12, Doug_McMa...@yahoo.com wrote:
> On Aug 15, 4:08 am, Summercooln...@gmail.com wrote:
>
> > I wonder instead of just brainstorming, there probably is
> > a very standard and a simple way to do database schema design.
>
> > let's say we are doing a website. the user can go over
> > and type in the movie name and zipcode, and the website
> > will return all the theaters showing that movie and at what
> > time, for theaters in THAT zipcode only (for simplicity).
>
> > so how do we just start and use a standard method that
> > can be simple and very accurate to ensure good tables design?
>
> You could start with atomic pieces of information and then look for
> relationships between them. For example, list Movies, Theaters, and
> ZipCodes on the board. Then it should be clear that there is a many-
> to-many relationship between Movies and Theaters, so you can add a
> relation for that. Then it should also be clear that there is a many-
> to-one relationship between ZipCodes and Theaters, so you can create a
> foreign key from Theaters to ZipCodes. It's not a substitute for real
> design work, but it might be enough for the 2-minute interview quiz.

Wouldn't it be one-to-many between ZipCodes and Theatres or do you
really have theatres that big?

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1187191510.809376.63070@b79g2000hse.googlegro ups.com...
> On 15 Aug, 16:12, Doug_McMa...@yahoo.com wrote:
> > On Aug 15, 4:08 am, Summercooln...@gmail.com wrote:
> >
> > > I wonder instead of just brainstorming, there probably is
> > > a very standard and a simple way to do database schema design.
> >
> > > let's say we are doing a website. the user can go over
> > > and type in the movie name and zipcode, and the website
> > > will return all the theaters showing that movie and at what
> > > time, for theaters in THAT zipcode only (for simplicity).
> >
> > > so how do we just start and use a standard method that
> > > can be simple and very accurate to ensure good tables design?
> >
> > You could start with atomic pieces of information and then look for
> > relationships between them. For example, list Movies, Theaters, and
> > ZipCodes on the board. Then it should be clear that there is a many-
> > to-many relationship between Movies and Theaters, so you can add a
> > relation for that. Then it should also be clear that there is a many-
> > to-one relationship between ZipCodes and Theaters, so you can create a
> > foreign key from Theaters to ZipCodes. It's not a substitute for real
> > design work, but it might be enough for the 2-minute interview quiz.
>
> Wouldn't it be one-to-many between ZipCodes and Theatres or do you
> really have theatres that big?
>

I know of a theater in northern Vermont/southern Quebec that straddles the
international border.

An unusual case, though. Not worth altering the model.

> You could start with atomic pieces of information and then look for
> relationships between them. For example, list Movies, Theaters, and
> ZipCodes on the board. Then it should be clear that there is a many-
> to-many relationship between Movies and Theaters, so you can add a
> relation for that. Then it should also be clear that there is a many-
> to-one relationship between ZipCodes and Theaters, so you can create a
> foreign key from Theaters to ZipCodes. It's not a substitute for real
> design work, but it might be enough for the 2-minute interview quiz.

will zipcode be just a property (a field) inside the theater table,
or will zipcode need to be a table itself?

> I know of a theater in northern Vermont/southern Quebec that straddles the
> international border.

and can you enter at the Vermont side and go to Quebec after the
movie.

> I know of a theater in northern Vermont/southern Quebec that straddles the
> international border.

and can you enter at the Vermont side and go to Quebec after the
movie.

On Aug 15, 8:25 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 15 Aug, 16:12, Doug_McMa...@yahoo.com wrote:
>
>
>
> > On Aug 15, 4:08 am, Summercooln...@gmail.com wrote:
>
> > > I wonder instead of just brainstorming, there probably is
> > > a very standard and a simple way to do database schema design.
>
> > > let's say we are doing a website. the user can go over
> > > and type in the movie name and zipcode, and the website
> > > will return all the theaters showing that movie and at what
> > > time, for theaters in THAT zipcode only (for simplicity).
>
> > > so how do we just start and use a standard method that
> > > can be simple and very accurate to ensure good tables design?
>
> > You could start with atomic pieces of information and then look for
> > relationships between them. For example, list Movies, Theaters, and
> > ZipCodes on the board. Then it should be clear that there is a many-
> > to-many relationship between Movies and Theaters, so you can add a
> > relation for that. Then it should also be clear that there is a many-
> > to-one relationship between ZipCodes and Theaters, so you can create a
> > foreign key from Theaters to ZipCodes. It's not a substitute for real
> > design work, but it might be enough for the 2-minute interview quiz.
>
> Wouldn't it be one-to-many between ZipCodes and Theatres or do you
> really have theatres that big?

You're right, I said that backward, sorry, though the fkey would still
be from Theater to ZipCode as stated.

On Aug 15, 4:39 pm, Summercooln...@gmail.com wrote:
> > You could start with atomic pieces of information and then look for
> > relationships between them. For example, list Movies, Theaters, and
> > ZipCodes on the board. Then it should be clear that there is a many-
> > to-many relationship between Movies and Theaters, so you can add a
> > relation for that. Then it should also be clear that there is a many-
> > to-one relationship between ZipCodes and Theaters, so you can create a
> > foreign key from Theaters to ZipCodes. It's not a substitute for real
> > design work, but it might be enough for the 2-minute interview quiz.
>
> will zipcode be just a property (a field) inside the theater table,
> or will zipcode need to be a table itself?

That is a design question. It depends whether you want to maintain a
set of unique zipcodes to validate against ond choose from, whether
there is more information you can store against each zipcode (city?
state?), and so on.

btw just to nitpick, it could be an attribute of an entity (if we are
talking about a logical model) or a column of a table (if we are
talking about a physical model), but not a "field". Fields belong in
files (or else they are green things with cows in them).

On Aug 15, 8:40 am, Summercooln...@gmail.com wrote:
> > You could start with atomic pieces of information and then look for
> > relationships between them. For example, list Movies, Theaters, and
> > ZipCodes on the board. Then it should be clear that there is a many-
> > to-many relationship between Movies and Theaters, so you can add a
> > relation for that. Then it should also be clear that there is a many-
> > to-one relationship between ZipCodes and Theaters, so you can create a
> > foreign key from Theaters to ZipCodes. It's not a substitute for real
> > design work, but it might be enough for the 2-minute interview quiz.
>
> will zipcode be just a property (a field) inside the theater table,
> or will zipcode need to be a table itself?

Something to ask your interviewer. As you discover relationships you
need to make that decision. The ZipCode is a domain. Since there's a
limited number of them, you may want a table of them just to capture
the domain, support a poplist on the website, capture other properties
of the ZipCode like an approximate name for the location, etc.

William Robertson wrote:

> On Aug 15, 4:39 pm, Summercooln...@gmail.com wrote:
>

[snip]

> btw just to nitpick, it could be an attribute of an entity (if we are
> talking about a logical model) or a column of a table (if we are
> talking about a physical model), but not a "field". Fields belong in
> files (or else they are green things with cows in them).

This is why cross-posts are totally useless. Entity=logical?!? Yikes!

BTW my earlier mistake shows the danger of making assumptions. For
example the users of the system may have had in mind a relationship
where a theater is listed by all zip codes for which it's considered
"close enough", implying a many-to-many. Or, it could be that a zip
code is assigned a lat/long coordinate or bounding-box lat/long
coordinates, as are all theaters, and the query is supposed to do some
sort of spatial operation, implying no direct relationship at all
between theaters and zip codes.