Dear all,

I have the following tables:
1.) shop, 2.) district and 3.) plaza
The relationships are defined as follows:
1.) 1 district may have zero or more plazas
2.) 1-M shop(s) are located in one district
3.) 1 Shop may exist in many districts
Optionally:
2.) 0-M shop(s) are located in one Plaza
3.) 1 Shop may exist in many Plaza

The design has an issue because a plaza is optional; so a shop may or
may not exist in a plaza while the shop must at least exist in one
district.
when i decomposite the many-to-many relationships i would create a
table shops_districts which is a weak entity.
I would also create a relationship between districts and plazas in
which i create a foreign key in the plaza table where it can be NULL
indicating its optionality.
However, due too this optionality i'm having an issue in the design and
would like to create a good design that spares the creation a
many-to-many relation shop between plaza table and shop table and again
a many-to-many relationship between the district table and shop table
and again a final 1-to-many relation between the district table and
plaza table.

Any suggestions?

On Thu, 25 May 2006 23:23:32 -0700, coosa wrote:

Sure.

Create a dummy plaza for each district and give it a name like
"non plaza".


HTH
Jerry

I'll go closer;
A shop must exist PHYSICALLYin one district as a sub of cities.
My hierarchies (countries) -> (states) -> (cities) -> (districts) ->
(malls or plazas) are designed to enable advanced search options of
shop locations.
In my country, it's a common thing to see a huge collection of shops
related to mobile selling, pc hardware selling, ..etc. in one mall.
People would preferably want also to search for shops only located in
certain plazas as well.

Imagine a scenario like this: Search for all shops that exist in Plaza
"A";
Shop "A" as a company name exist in let's say 10 locations in the
country distributed in 4 cities; City "A" has 3 branches of Shop "A";
District "A" of city "A" has two locations, while District "B" has only
one and none of the districts belonging to City "A" has a Plaza. On the
other hand, City "B" has 2 branches of Shop "A" located in District "C"
and District "D" whereby District "B" DOES have a Plaza "A" where Shop
"A" is located; ...etc.
Like said, PHYSICALLY, the Shop in a Plaza is optional while it is
MANDATORAY in a District.
The business rules are perfectly fine.
My Issue is with implementation since i was never introduced before to
such a case.

Best regards

The issue with is the UNIQUE CONSTARINT ....
The relation between (Districts) and (Malls or Plazas) is One To Many
and the Plaza name shall be a unique constraint to prevent a mistake of
duplication by names.

coosa wrote:
Okay I see that now.

I would say "advanced search options" is a application feature
requirement, not a design requirement of the data model. Yes you want
to consider searching, but your hierarchy should be a consequence of
the entities. In this, it case seems right.

that's an attribute of the mall/plaza.

that's an application requirement. For the DB design this lets us know
there'll be queries with "WHERE plaza_mall_name='example' ..."
Does this imply the mall/plaza name is unique? IOW, there is only one
plaza "A" in all the countries?

your other post did not mention Plaza being optional for the shop
location.

My point has been to think about the entities and their attributes.
That is database design. It seems to me you should forget for a while
the search hierarchy. there are three basic entities you are dealing
with:
Districts, Shops, and Plazas

What are the attributes of a District?
name? city? state? country? geographic boundary? population? sales tax
rate? Phone exchange? flag? legal system? Others?
Which of those forms the primary key? Name alone? Name, city, state
combined?

What are the attributes of a plaza?
name? district name? type (open strip plaza, enclosed shopping mall)?
product type (all shops in this place sell the same type of product,
PCs, cellular phone services, food,...)
Which attributes form the primary key?

What are the attributes of a shop?
brand name? location name? ("welcome to the Hilton Hotel Westbridge")
Both? parent company name? (e.g. Taco Bell is owned by PEPSICO).
Location (District)? plaza location? Shopping hours? average daily
cashflow? Products sold? type of products sold? phone number? manager
name? owner name?
which attributes form the primary key?

Whether you create a relation entity between plazas and shops or make
that an optional attribute of shops is somewhat of a style issue.
Initially I would make it an attribute of shops,and let normalization
determine whether to split it out or not.

HTH,
ed