standard and easy way to do schema design

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?

Re: standard and easy way to do schema design

On Aug 15, 1:08 pm, 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?

The standard method is to make a functional design based upon the end-
users information needs.
The determine your entities and the relations between entities, then
you convert your entities to tables, and your functions to screens.
This is basically what products like Designer and JDeveloper do.
Entity Relationship Modeling is a department of Information Science.
What you describe is taking a pencil, sticking your thumb in the air,
and starting 'somewhere'. This is called Woodshed Modeling.
Regrettably this is how most applications are build nowadays. This
won't work, ever.

--
Sybrand Bakker
Senior Oracle DBA

Re: standard and easy way to do schema design

On Aug 15, 1:30 pm, sybrandb wrote:
> On Aug 15, 1:08 pm, 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.

Yes, the way is indeed simple. First, read books like "Handbook of
Relational Database Design"

http://www.amazon.com/Handbook-Relational-Database-Candace-F leming/dp/0201114348/ref=pd_bbs_sr_1/102-3307099-5856116?ie= UTF8&s=books&qid=1187036320&sr=1-1

After that, you need a few month of making mistakes. After that,
things become very standard and simple - just as with writing software
in general >-:)

Re: standard and easy way to do schema design

wrote in message
news:1187176120.886269.130090@z24g2000prh.googlegroups.com.. .
> 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?
>

If you want to get an overview of what data modeling and table design are
all about, take a look at the outline in this website:

http://www.utexas.edu/its/windows/database/datamodeling/dm/o verview.html

These techniques are overkill for the problem you outlined. But if the
information needs grow, as they always do, you will soon be in the
territory where you need these techniques or something like them.

Re: standard and easy way to do schema design

On Aug 15, 5:21 am, "David Cressey" wrote:
> wrote in message
>
> news:1187176120.886269.130090@z24g2000prh.googlegroups.com.. .
>
> > 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).

Nowadays when I go for interviews, many interviewers hope that
I can produce a schema in just 2, 3 minutes...

So I wonder for the example above, is there a 2 minute way of thinking
to produce the solution?

Re: standard and easy way to do schema design

Summercoolness@gmail.com wrote:

> On Aug 15, 5:21 am, "David Cressey" wrote:
>
>> wrote in message
>>
>>news:1187176120.886269.130090@z24g2000prh.googlegroups.com ...
>>
>>>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).
>
> Nowadays when I go for interviews, many interviewers hope that
> I can produce a schema in just 2, 3 minutes...
>
> So I wonder for the example above, is there a 2 minute way of thinking
> to produce the solution?

I suggest you stop interviewing for idiots. They won't advance your
career any.

Re: standard and easy way to do schema design

On Aug 15, 7:57 am, Summercooln...@gmail.com wrote:
> On Aug 15, 5:21 am, "David Cressey" wrote:
>
> > wrote in message
>
> >news:1187176120.886269.130090@z24g2000prh.googlegroups.com. ..
>
> > > 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).
>
> Nowadays when I go for interviews, many interviewers hope that
> I can produce a schema in just 2, 3 minutes...
>
> So I wonder for the example above, is there a 2 minute way of thinking
> to produce the solution?

The less time you spend in design the more time you'll spend later in
development costs as your 'model' will show its weaknesses (such as
being totally unscalable) and require MORE work than it would have
taken to build the application properly.

I suggest you stop being lazy and start being smart.


David Fitzjarrell

Re: standard and easy way to do schema design

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.

Re: standard and easy way to do schema design

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?

Re: standard and easy way to do schema design

"Captain Paralytic" wrote in message
news:1187191510.809376.63070@b79g2000hse.googlegroups.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.

Re: standard and easy way to do schema design

On 15 Aug, 16:29, "David Cressey" wrote:
> "Captain Paralytic" wrote in message
>
> news:1187191510.809376.63070@b79g2000hse.googlegroups.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.- Hide quoted text -
>
> - Show quoted text -

I wondor, if the 2 countries have different views on censorship, can
certain parts of a scene only be played on one part of the stage?

Re: standard and easy way to do schema 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.

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

Re: standard and easy way to do schema 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.

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

Re: standard and easy way to do schema design

> 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.

Re: standard and easy way to do schema design

> 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.

Re: standard and easy way to do schema design

On Aug 15, 8:25 am, Captain Paralytic 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.

Re: standard and easy way to do schema design

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).

Re: standard and easy way to do schema design

Post removed (X-No-Archive: yes)

Re: standard and easy way to do schema design

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.

Re: standard and easy way to do schema design

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!

Re: standard and easy way to do schema design

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.

Re: standard and easy way to do schema design

On Aug 15, 8:57 am, Summercooln...@gmail.com wrote:
> On Aug 15, 5:21 am, "David Cressey" wrote:
>
> > wrote in message
>
> >news:1187176120.886269.130090@z24g2000prh.googlegroups.com. ..
>
> > > 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).
>
> Nowadays when I go for interviews, many interviewers hope that
> I can produce a schema in just 2, 3 minutes...
>
> So I wonder for the example above, is there a 2 minute way of thinking
> to produce the solution?


yes I have asked (and been asked) to design a simple schema in a few
minutes. There are a couple of things that come out in that kind of
interview question:

Experience: if you have seen similar problems before, then you will
come up with a solution fairly quickly. If you claim say " I have 5
years design experience", you should be able to outline a solution in
a few minutes. (2minutes is only enough time to draw the solution).

Mental Agility: the question also is to test how you think under
pressure with a deadline. Your ability to find any solution in a short
time and possibly see the limits in your own design are indicators of
your mental skills.

Communication skills: do you take the given question and just start
writing your solution?
or do you ask more questions about how the data will be used?

It's not necessarily a bad interview question.

To get better at answering such questions: PRACTICE.

Ed

Re: standard and easy way to do schema design

On Wed, 15 Aug 2007 10:53:27 -0700, Doug_McMahon@yahoo.com wrote:

>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.

If you really want to play with the tradeoffs of designing such a
database it might be worth your time looking at an application that
already does what you are thinking about. For example, look
http://movies.zap2it.com/movies/browse/movie/0,1259,---,00.h tml and
you will quickly realize that a zip-code table is required, and what
sort of results you might need from a proximity test. Or you might
even decide not to reinvent the wheel. 8-)

Roy Harvey
Beacon Falls, CT

Re: standard and easy way to do schema design

>> a very standard and a simple way to do database schema design. <<

No. But there are some design patterns which you can see in Hay's
book.

Little horror story about outsourcing. Six months into a project, the
American gets a call from his Indian counterpart, who asks "Is
bookkeeping in America done by cash or accrual system?" There is a
pause, and the American says "Accrual"; the Indian responds "Thank
Ganesha!-- something in Hindi to the other people on his side of the
phone line, followed by happy sounds -- Good bye!"

Ganesh is the Hindu god with an elephant's head who protects those who
suffer unjustly -- that makes him the patron saint of developers with
vague specs :)

Having said that, there are development methods (NOT Methodologies --
I agree with Larry Constantine that Methodology is the study of
methods). A method is what we do when we have no idea what to do
next. Do you like RUP? ER (Chen, not the TV show)? Did you start
with an ORM diagram? Are you Ambler Agile? etc.

Re: standard and easy way to do schema design

>> Nowadays when I go for interviews, many interviewers hope that I can produce a schema in just 2, 3 minutes... <<

Grab a felt-tip pen and paper napkin -- the drawing tool and surface
is important. Draw a box for every physical object they name anywhere
on the page. Draw other boxes or shapes for any kind of relationship
and connect them to the members of the first set of meaningless blobs,
while bullshitting with a lot of techno-babble.

"Here is a table for the automobiles, one for squids, and -- of course
-- one for Britney Spears since she will be very important in the
industry next week. If she marries a squid, we
will have a marriage relationship table here, yadda, yadda, yadda..."

Now, spill water on the napkin or put it in your pocket.

========

I just re-read this before posting. I was trying to be humorous but
it really reads like "stand up tragedy" because I have been called in
to shops where they "Code first, design later" just like we did
decades ago and with the same crappy systems as a result.

The research into legal problems and industry standards for a new
system can weeks or months even in an established system. Thank
Google we can do it 1000 times faster today!

Re: standard and easy way to do schema design

>> The less time you spend in design the more time you'll spend later in development costs .. <<

Barry Boehm et al during the Software Engineering early research days
showed an order of magnitude in cost from one step in the DoD 2167A
and 2167B methods when you corrected errors.

Re: standard and easy way to do schema design

> a simple way to do database schema design.
> user can type in the movie name and zipcode,
> return all the theaters showing that movie
> and at what time, for theaters in THAT zipcode.

Below is how one could do it using dbd, a lightweight, memory-resident
database. Unlike RMDBs that store values in table-like structures, dbd
stores data as a network of nodes where each node is equivalent to an
AND gate. Below script enters sample data and runs desired query:

(; Create movies)
(new 'underdog 'movie)
(new 'cars 'movie)
(new 'superman 'movie)
(new 'alien 'movie)

(; Create theatres)
(new 'amc1 'theatre)
(new 'amc2 'theatre)
(new 'amc3 'theatre)
(new 'cinemark1 'theatre)
(new 'cinemark2 'theatre)
(new 'cinemark3 'theatre)

(; Create zipcodes)
(new '22222 'zipcode)
(new '33333 'zipcode)

(; Create movie times)
(new '1700 'time)
(new '1900 'time)
(new '2100 'time)

(; Set threatre zipcodes)
(set amc1 zipcode 22222)
(set amc2 zipcode 33333)
(set amc3 zipcode 33333)

(set cinemark1 zipcode 22222)
(set cinemark2 zipcode 22222)
(set cinemark3 zipcode 33333)

(new 'shows 'verb)

(set amc1 shows underdog at 1700)
(set amc1 shows underdog at 2100)

(set amc1 shows superman at 1900)
(set amc1 shows superman at 2100)


(set amc2 shows cars at 1900)
(set amc2 shows cars at 2100)

(set amc2 shows alien at 1900)
(set amc2 shows alien at 2100)


(set amc3 shows underdog at 1900)
(set amc3 shows alien at 2100)


(set cinemark1 shows cars at 1700)
(set cinemark1 shows cars at 2100)

(set cinemark1 shows alien at 1900)
(set cinemark1 shows alien at 2100)


(set cinemark2 shows superman at 1700)
(set cinemark2 shows superman at 2100)

(set cinemark2 shows cars at 1900)
(set cinemark2 shows cars at 2100)


(set cinemark3 shows underdog at 2100)
(set cinemark3 shows superman at 2100)


(; Get all theatres/times in zip 33333 that shows alien)
(; Following query gets following 3 nodes:
amc2 shows alien at 1900
amc2 shows alien at 2100
amc3 shows alien at 2100)
(get (get * zipcode 33333)
shows alien at (get time instance *))


For more info, see www.dbfordummies.com

Re: standard and easy way to do schema design

Post removed (X-No-Archive: yes)

Re: standard and easy way to do schema design

On Aug 15, 2:59 pm, Neo wrote:
> > a simple way to do database schema design.
> > user can type in the movie name and zipcode,
> > return all the theaters showing that movie
> > and at what time, for theaters in THAT zipcode.

So let's say the problem is just that simple, without needing zipcode
related information (such as the name of the district)...

We can write

Movies
id
title
showtimes and in which theaters >>>>>>>
duration
description

Theaters
id
name
zipcode
phone number
address line 1
address line 2
movies showing there and their showtimes >>>>>>>>

and we put the ">>>>>>>>" for things that are "multiple items".
(indicated by us writing it in plural, such as showtimes)
and for any ">>>>>>>>" line, we will need to create a separate table,
if that table doesn't already exist.

and for those ">>>>>>>>" lines, we will simply remove them from the
original table.

so in our case, we create a new table

Showings
movie_id
theater_id
movie start time

and remove those ">>>>>>>>" lines in the original tables "Movies" and
"Theaters".

and that will complete the basic task? (assuming no future function
is needed).

Re: standard and easy way to do schema design

On Aug 15, 4:08 am, Summercooln...@gmail.com wrote:

> 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).


[i added the date to the Showings table below... because we want
showtimes for today, or tomorrow, etc]

So let's say the problem is just that simple, without needing zipcode
related information (such as the name of the district)...

We can write

Movies
id
title
showtimes and in which theaters >>>>>>>
duration
description

Theaters
id
name
zipcode
phone number
address line 1
address line 2
movies showing there and their showtimes >>>>>>>>

and we put the ">>>>>>>>" for things that are "multiple items".
(indicated by us writing it in plural, such as showtimes)
and for any ">>>>>>>>" line, we will need to create a separate table,
if that table doesn't already exist.

and for those ">>>>>>>>" lines, we will simply remove them from the
original table.

so in our case, we create a new table

Showings
movie_id
theater_id
movie start time
movie show date

and remove those ">>>>>>>>" lines in the original tables "Movies" and
"Theaters".

and that will complete the basic task? (assuming no future function
is needed).

Re: standard and easy way to do schema design

> and for any ">>>>>>>>" line, we will need to create a separate table,
> if that table doesn't already exist.
>
> and for those ">>>>>>>>" lines, we will simply remove them from the
> original table.


I think one correction is this:

When there are plurals, then that means yes, there needs to be a
separate table for those items, but if it is actually one-to-many
relationship, then those items would most likely already be in another
existing table.

Example:

Suppliers
id
name
products it supply >>>>>>>
address
phone
etc

So we need another table for "Products"

Assuming 1 product can only come from 1 supplier (cannot come from 2
or more suppliers), so we don't need an additional table besides
"Suppliers" and "Products" tables.

The only situation where we need an additional table is that when the
2 entities are related by the many-to-many relation, such as one movie
can show in many theaters, and one theater can show many movies. In
that case, we need this additional table in the middle, so that the
"Movies" table is connected to it in a "one-to-many" relation, and the
"Theaters" table is also connect to it in a "one-to-many" relation.

Thus, it seems like any "line" draw between two tables can only be 1-
to-1 or 1-to-many, but not many-to-many, because the many-to-many
relation becomes a table itself.

Re: standard and easy way to do schema design

-CELKO- wrote:


> Ganesh is the Hindu god with an elephant's head who protects those who
> suffer unjustly -- that makes him the patron saint of developers with
> vague specs :)


Amen to that - if the said Ganesh accepts grovelling from one
of Christian origin - though mind you, I thought Saint Jude
"When all other avenues are closed, he is the one to call upon,
and his help often comes at the last moment." was the boy to
invoke when things went awry during an IT project - the last
minute help ususally being an assignment to a different company,
where they had actually pause for 5 minutes before lashing
themselves into a frenzy of code production (for some reason
the mating habits of various lower vertebrates come to mind
here, as do calices and all sorts of sundry medieval mortification
devices).




Paul...