On Aug 15, 8:57 am, Summercooln...@gmail.com wrote:
> On Aug 15, 5:21 am, "David Cressey" <cresse...@verizon.net> wrote:
>
> > <Summercooln...@gmail.com> wrote in message
>
> >news:1187176120.886269.130090@z24g2000prh.googleg roups.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

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/brow...59,---,00.html 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

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

>> 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
<insert name here> 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!

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

> 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

On Aug 15, 2:59 pm, Neo <neo55...@hotmail.com> 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).

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

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

On Aug 15, 2: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?

Please take a look at MicroOLAP Database Designer -
http://microolap.com/products/database/mysql-designer/