hi
I was searching in net and I can't find enough information about it:
for example I have 2 tables:
tableA (idA int PK, )
is in relation 1 to N with
tableB (idB int, idA int FK, )
do You meet something like that: foreign key idA and column idB became
primary key in tableB?
if You say 'yes', when I should use this solution?
In my opinion this is strange solution, because if I have N tables:
tabA (1) to (N) tabB
tabB (1) to (N) tabC
in tabB I have primary key (idB, idA)
in tabC I have primary key (idC, idB, idA), where idB, idA are FK from tabB,
ect
in tabN I have a few keys, which haven't any sense, because that keys cross
from other tables as PK
please, help me
best regard
RoB

RoB wrote:
hi

I was searching in net and I can't find enough information about it:

for example I have 2 tables:
tableA (idA int PK, )
is in relation 1 to N with
tableB (idB int, idA int FK, )

do You meet something like that: foreign key idA and column idB became
primary key in tableB?
if You say 'yes', when I should use this solution?

In my opinion this is strange solution, because if I have N tables:
tabA (1) to (N) tabB
tabB (1) to (N) tabC

in tabB I have primary key (idB, idA)
in tabC I have primary key (idC, idB, idA), where idB, idA are FK from tabB,
ect

in tabN I have a few keys, which haven't any sense, because that keys cross
from other tables as PK
please, help me
--
best regard
RoB

rather than THERY, think about real examples.

Consider a wire line phone system. Area codes are defined for certain
reagions. So you start with a table what defines those regions
regions (AreaCode, Boundary, RegionName) PK AreaCode

within each region are several exchange switches
Exchanges (AreaCode, Exchange, switchname, switchtype,)
FK AreaCode,
PK AreaCode, Exchange

See the pattern?

This can happen in lots of things that are hierarchical. Consider a
mapping database that includes searching addresses. You wouldn't simply
have one address table. You can simplify thins with a hierarchy like

States(stateabbrev, statefullname,) PK stateabbrev

Cities (cityabbrev, cityfullname,stateabbrev,)
FK stateabbrev (enables validation of state values)
PK cityabbrev, stateabbrev

And so on thru the hierarchy.

Sounds like you need to back off from tables and go to the design level
What are the entities that have relations that "don't make sense"?
Maybe you missed some important relations along the way that shows up
at the entity you are dealing with now. come back with more specific
details so we can help.

HTH
ed

What are the entities that have relations that "don't make sense"?
Maybe you missed some important relations along the way that shows up
at the entity you are dealing with now. come back with more specific
details so we can help.
real example: diagram ERD has 3 example tables:
providers (prov_id, )
customers(cust_id, prov_id, )
addresses(addr_id, cust_id, prov_id, ) -- only customer addresses

relation beetwen that tables is: 1 provider has 1n customers, 1 customer
has 1n addresses
in table customers prov_id is FK from providers. Primary key in this table
is (cust_id, prov_id)

In table addresses FK is (cust_id, prov_id). it's good, because this is PK
in customers.
But prov_id is not in any relation whit customer address.
This solution create a problem with implementation, may I right?

best regards
RoB

RoB wrote:
What are the entities that have relations that "don't make sense"?
Maybe you missed some important relations along the way that shows up
at the entity you are dealing with now. come back with more specific
details so we can help.
real example: diagram ERD has 3 example tables:
providers (prov_id, )
customers(cust_id, prov_id, )
addresses(addr_id, cust_id, prov_id, ) -- only customer addresses

relation beetwen that tables is: 1 provider has 1n customers, 1 customer
has 1n addresses
in table customers prov_id is FK from providers. Primary key in this table
is (cust_id, prov_id)

In table addresses FK is (cust_id, prov_id). it's good, because this is PK
in customers.
But prov_id is not in any relation whit customer address.
This solution create a problem with implementation, may I right?
--
best regards
RoB

Brief rant: Pseudokeys like customer_id, Prov_id IMH change the data
model from relational to network. I specifically tried to give an
address which doesn't use pseudokeys. Sometimes it cannot be avoided,
but many times it can be.
Now That I got that out of my system, lets look at your specific
question.

I think you are right about the province. So why isn't there a relation
between address and province? like I said, you should go back to your
logical data model and review it.

What is an address in your model? With customer as part of the primary
key it seems this emntity is misnamed. What if a customer moves? How
does the new address get assigned? Is there ever a need to know an
address which doesn't have a customer?

Can an address exist outside a province? (Do you have any customers
from outside your country?)

So think about it at that higher level first and fix it there. Then
when you go back to building the tables, at least consider getting rid
of some of those ID keys. (Isn't there an abbreviation for the province
that could be used? I really cannot see any reason to use a ID for the
province, for example.)

Lastly, you won't always be in a position to use a Foreign Key
constraint to deal with this. The province is an example again. Most
likely you'll want the full province name as the primary key, so the
province abbreviation is just an attribute in the provinces table so
you cannot make a foreign key contraint to it from the address table.
But you may be able to use a check constraint.

HTH,
Ed

I think you are right about the province.
no no :) In fact, I have table provinces in my ERD diagram, but in my
example I skip that table, because I think, it's not very important in my
question.
I wrote about 3 tables: providers (not provinces), customers and addresses

So why isn't there a relation between address and province?
I couldn't wrote all ERD diagram, because it's very big diagram (about 40-50
tables)

ok, I try explain (this time, I use 3 other example tables with province :)
:
coutries( country_id, country_code, country_name)
provinces( province_id, province_name, country_id) -- in this table PK
is (province_id, country_id)
addresses( addr_id, province_id, country_id) -- in this table
PK is (addr_id, province_id, country_id)

relation is simple: 1 country has N provinces, 1 province has N addresses
in this example, addresses PK is "good" PK, because all keys are related
with address, but it's not the smallest key, which can be used.
The smallest primary key is addr_id (because it's auto_increment key)

In my previus example I tried show 3 tables, where provider_id isn't any
sense in table addresses (in my opinion)

Is there ever a need to know an address which doesn't have a customer?
No, address is always in relation with 1 customer

Can an address exist outside a province? (Do you have any customers
from outside your country?)
I have customers outisde me country. Addresses can't exist outside a
province, because I have countries and provinces table

So think about it at that higher level first and fix it there. Then
when you go back to building the tables, at least consider getting rid
of some of those ID keys. (Isn't there an abbreviation for the province
that could be used? I really cannot see any reason to use a ID for the
province, for example.)
exactly, I can't see any reason too :) but it's not my ERD diagram and I
decide ask in this group, what this group think about this solution

thanks for discusion, I haven't any other question.

best regard
RoB

RoB wrote:
I think you are right about the province.
no no :) In fact, I have table provinces in my ERD diagram, but in my
example I skip that table, because I think, it's not very important in my
question.
I wrote about 3 tables: providers (not provinces), customers and addresses

So why isn't there a relation between address and province?
I couldn't wrote all ERD diagram, because it's very big diagram (about 40-50
tables)

ok, I try explain (this time, I use 3 other example tables with province :)
:
coutries( country_id, country_code, country_name)
provinces( province_id, province_name, country_id) -- in this table PK
is (province_id, country_id)
addresses( addr_id, province_id, country_id) -- in this table
PK is (addr_id, province_id, country_id)

relation is simple: 1 country has N provinces, 1 province has N addresses
in this example, addresses PK is "good" PK, because all keys are related
with address, but it's not the smallest key, which can be used.
The smallest primary key is addr_id (because it's auto_increment key)

THAT'S YUR PRBLEM! You confuse a pseudokey (addr_id) with a real
Primary key. Until you understand the difference you will always have
this problem.

I would use something like this:
coutries( country_code, country_name)
-- in this table PK is country code (where country code is a
uniqure abbreviation, like USA, GER, PRC)
provinces( province_code, province_name, country_id)
-- in this table PK is (province_code, country_id) where province
code is a unique abbreviation (NT, CAN = Canada, for example)
clientaddresses( line1,line2,province_id, country_id)
-- in this table PK is (line1,line2, province_id,
country_id) where these represent the address as entered by the client
(so they might not necessarily be valid postal addresses)

In my previus example I tried show 3 tables, where provider_id isn't any
sense in table addresses (in my opinion)

Is there ever a need to know an address which doesn't have a customer?
No, address is always in relation with 1 customer

Can an address exist outside a province? (Do you have any customers
from outside your country?)
I have customers outisde me country. Addresses can't exist outside a
province, because I have countries and provinces table

So think about it at that higher level first and fix it there. Then
when you go back to building the tables, at least consider getting rid
of some of those ID keys. (Isn't there an abbreviation for the province
that could be used? I really cannot see any reason to use a ID for the
province, for example.)
exactly, I can't see any reason too :) but it's not my ERD diagram and I
decide ask in this group, what this group think about this solution

Then ask the data modeler why he's introducing pseudo keys into the
model. is he just an ACCESS programmer, or a real datamodeler? It's
isn't easy but sometimes you have to push back a bad data model. The
province ID adds no data integrity to your system. It doesn't save much
data space either. so why use it?

In comparison, and address ID might be justified on the grounds that
you have millions of clients and storing the full PK of the address
consumes too much space. Here the trade off is between a 32bit integer
ID and the size of the PK fields of address (say each line=60
characters, province code=4chars, and country code=4chars, for 128bytes
at least) so there is a savings of over 120bytes per customer row.
downside is the extra join when you need the actual address
information.

thanks for discusion, I haven't any other question.

best regard
RoB

Sounds like you have a long road ahead of you. Hope my comments help.
Ed
(I'm actually surprised no one else has commented on this thread. Maybe
they all went on Christmas vacation early?)

19 Dec 2005 11:19:37 -0800, "Ed Prochak"
<ed.prochak@magicinterface.comwrote:

[snip]

>(I'm actually surprised no one else has commented on this thread. Maybe
>they all went on Christmas vacation early?)

I just got back from my Christmas visit to my mom, but I do not
think that everyone is as quick as I am. Ah, that may be the
explanation: office parties are slowing them down.

Sincerely,

Gene Wirchenko

RoB wrote:

[snip]

real example: diagram ERD has 3 example tables:
providers (prov_id, )
customers(cust_id, prov_id, )
addresses(addr_id, cust_id, prov_id, ) -- only customer addresses

[snip]

But prov_id is not in any relation whit customer address.
This solution create a problem with implementation, may I right?

Well, it depends. It could be an important relation. Perhaps at some
point, for example, you would like to ask a question of the data like:
'What countries/provinces does provider X have most of their customers
in?' or 'Which country/province gets the most business from providers?'
course, if you don't care about these things (and others that I
can't think of at the moment) than I'd have to agree with Ed that you
may have to look more closely at your data model.

Todd