By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,633 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,633 IT Pros & Developers. It's quick & easy.

Data Repeated

P: n/a
Dears,

I have many feilds in my tables that are left blank for some records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad

Apr 23 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
So the junction table will have fields for CityID and Feature, and you will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower

What's wrong with that?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<fa******@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
Dears,

I have many feilds in my tables that are left blank for some records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.

Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.

I hope you got me,
I appreciate your help,

Farhaad
Apr 23 '07 #2

P: n/a
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
So the junction table will have fields for CityID and Feature, and you will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower

What's wrong with that?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
Dears,
I have many feilds in my tables that are left blank for some records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -

- Show quoted text -
Sir,

I just want to know if we can avoid the repition of data in the sub
table.

Thanks alot,

Apr 23 '07 #3

P: n/a
No. That's the right structure.

Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities and
features.)

That is the standard way to resolve a many-to-many relation into a pair of
one-to-many relations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<fa******@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>So the junction table will have fields for CityID and Feature, and you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower

What's wrong with that?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegr oups.com...
Dears,
I have many feilds in my tables that are left blank for some records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -

- Show quoted text -

Sir,

I just want to know if we can avoid the repition of data in the sub
table.

Thanks alot,
Apr 23 '07 #4

P: n/a
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's the right structure.

Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities and
features.)

That is the standard way to resolve a many-to-many relation into a pair of
one-to-many relations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
So the junction table will have fields for CityID and Feature, and you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
What's wrong with that?
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
Dears,
I have many feilds in my tables that are left blank for some records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the sub
table.
Thanks alot,- Hide quoted text -

- Show quoted text -
Thanks alot for the help and Sir!

Can a primary key have many foreing keys? and can these foreign tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).

I hope you got some Idea from the above example, Suppose we have three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.

I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?

I appreciate your extraordinary intelligence in databases and helping
others,

Apr 24 '07 #5

P: n/a
No. That's not right.

You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River

The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<fa******@gmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>No. That's the right structure.

Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities and
features.)

That is the standard way to resolve a many-to-many relation into a pair
of
one-to-many relations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
So the junction table will have fields for CityID and Feature, and you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the sub
table.
Thanks alot,- Hide quoted text -

- Show quoted text -

Thanks alot for the help and Sir!

Can a primary key have many foreing keys? and can these foreign tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).

I hope you got some Idea from the above example, Suppose we have three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.

I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?

I appreciate your extraordinary intelligence in databases and helping
others,
Apr 24 '07 #6

P: n/a
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's not right.

You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River

The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegro ups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's the right structure.
Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities and
features.)
That is the standard way to resolve a many-to-many relation into a pair
of
one-to-many relations.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
So the junction table will have fields for CityID and Feature, and you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
What's wrong with that?
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a lookup
feilds so only those options are chosen which fit to the table, some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the sub
table.
Thanks alot,- Hide quoted text -
- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and helping
others,- Hide quoted text -

- Show quoted text -
Thank you sir,

What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.

is that ok?

I thank you very much

Apr 24 '07 #7

P: n/a
No. that is not okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<fa******@gmail.comwrote in message
news:11**********************@r30g2000prh.googlegr oups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>No. That's not right.

You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River

The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's the right structure.
>Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities
and
features.)
>That is the standard way to resolve a many-to-many relation into a
pair
of
one-to-many relations.
>--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
So the junction table will have fields for CityID and Feature, and
you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices
in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the sub
table.
Thanks alot,- Hide quoted text -
>- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and helping
others,- Hide quoted text -

- Show quoted text -

Thank you sir,

What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.

is that ok?

I thank you very much
Apr 24 '07 #8

P: n/a
On Apr 24, 10:23 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
No. that is not okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.<farha...@gmail.comwrote in message

news:11**********************@r30g2000prh.googlegr oups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's not right.
You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River
The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous reply.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's the right structure.
Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities
and
features.)
That is the standard way to resolve a many-to-many relation into a
pair
of
one-to-many relations.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
So the junction table will have fields for CityID and Feature, and
you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
What's wrong with that?
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no), airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower) and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three choices
in
the feild. here if two cities have airports, airport is mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the sub
table.
Thanks alot,- Hide quoted text -
- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and helping
others,- Hide quoted text -
- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -

- Show quoted text -
Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.

Thank alot

Apr 24 '07 #9

P: n/a
Sorry: I'm out of suggestions.

You are closer to your data, and you know what you can and can't achieve. I
can't see it so all I can do is explain the normalized approach. If that
doesn't apply, you can take it from there, and it's time for me to move on
to answering other questions.

All the best

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<fa******@gmail.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
On Apr 24, 10:23 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>No. that is not okay.

news:11**********************@r30g2000prh.googleg roups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's not right.
>You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River
>The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous
reply.
>--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
No. That's the right structure.
>Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities
and
features.)
>That is the standard way to resolve a many-to-many relation into a
pair
of
one-to-many relations.
>--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
So the junction table will have fields for CityID and Feature,
and
you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the
table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no),
airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower)
and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three
choices
in
the feild. here if two cities have airports, airport is
mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the
sub
table.
Thanks alot,- Hide quoted text -
>- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign
tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have
three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and
helping
others,- Hide quoted text -
>- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -

- Show quoted text -

Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.

Thank alot
Apr 24 '07 #10

P: n/a
On Apr 24, 12:37 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Sorry: I'm out of suggestions.

You are closer to your data, and you know what you can and can't achieve. I
can't see it so all I can do is explain the normalized approach. If that
doesn't apply, you can take it from there, and it's time for me to move on
to answering other questions.

All the best

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<farha...@gmail.comwrote in message

news:11**********************@s33g2000prh.googlegr oups.com...
On Apr 24, 10:23 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
No. that is not okay.
>news:11**********************@r30g2000prh.googleg roups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
No. That's not right.
You need a Feature table, with a field like this:
FeatureID Text primary key
Then you enter *records* such as:
Airport
Tower
River
The junction table then has fields like this:
CityID relates to the primary key of the City table
FeatureID relates to the primary key of the Feature table.
So the records in this table look like the example in the previous
reply.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
No. That's the right structure.
Ultimately you have 3 tables:
Table 1 = a list of cities;
Table 2 = a list of features;
Table 3 = the junction table between the other two (matching cities
and
features.)
That is the standard way to resolve a many-to-many relation into a
pair
of
one-to-many relations.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
So the junction table will have fields for CityID and Feature,
and
you
will
have records like this:
CityID Feature
==== ======
2 Airport
2 River
3 Airport
3 Tower
What's wrong with that?
<farha...@gmail.comwrote in message
>news:11**********************@o5g2000hsb.googlegr oups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the
table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no),
airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower)
and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three
choices
in
the feild. here if two cities have airports, airport is
mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the
sub
table.
Thanks alot,- Hide quoted text -
- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign
tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have
three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and
helping
others,- Hide quoted text -
- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -
- Show quoted text -
Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.
Thank alot- Hide quoted text -

- Show quoted text -
Ok sir,

I again thank you for you help and I will rethink on my database
design and solve the problem.

regards,

farhaad

Apr 24 '07 #11

P: n/a
GH
It might be helpful if you provide specific examples of data you would
enter and how you are currently seeing repetition and blank fields for
Mr. Browne or someone else to better help you. It is not clear from
your descriptions how your data can best be normalized. Examples of
two or three cities with zero or more of your features, including
multiple of a single feature, can help someone else understand your
data structure. Generally when you have multiple tables with many-to-
many relationships, you need an intersection table to resolve each of
these. If your city can have zero or more hospitals, rivers,
airports, trade centers, etc., and each of these individual features
has its own table, then you should expect to see something like the
following table structure:

tblCity (CityID, CityName, etc...)

tblHospital (HospitalID, HospitalName, etc.)
tblCityHospital (CityID, HospitalID)

tblAirport (AirportID, AirportName, etc.)
tblCityAirport (CityID, AirportID)

tblRiver (RiverID, RiverName, etc.)
tblCityAirport (CityID, RiverID)

tblTradeCenter (TradeCenterID, TradeCenterName, etc.)
tblCityAirport (CityID, TradeCenterID)

This type of structure allows a single city to demonstrate NONE of a
feature as well as more than one of one or more features. This also
offers the ability to add other data elements specific to a particular
type of feature (rivers might want length or hospitals and trade
centers number of rooms or capacity). You can also include data
elements specific to the city/feature relationship, such as
tblCityRiver might include the length of river within the city (vice
the total river length, which is in that city's table). This may not
be the structure you need, but maybe it will help you think about the
best way to normalize your data yourself or present examples to the
newsgroup that can better help us help you.

- GH

On Apr 24, 5:40 am, farha...@gmail.com wrote:
>>news:11**********************@r30g2000prh.google groups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>No. That's not right.
>You need a Feature table, with a field like this:
> FeatureID Text primary key
>Then you enter *records* such as:
> Airport
> Tower
> River
>The junction table then has fields like this:
> CityID relates to the primary key of the City table
> FeatureID relates to the primary key of the Feature table.
>So the records in this table look like the example in the previous
>reply.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>No. That's the right structure.
>Ultimately you have 3 tables:
>Table 1 = a list of cities;
>Table 2 = a list of features;
>Table 3 = the junction table between the other two (matching cities
>and
>features.)
>That is the standard way to resolve a many-to-many relation into a
>pair
>of
>one-to-many relations.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>So the junction table will have fields for CityID and Feature,
>and
>you
>will
>have records like this:
> CityID Feature
> ==== ======
> 2 Airport
> 2 River
> 3 Airport
> 3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the
table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no),
airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower)
and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three
choices
in
the feild. here if two cities have airports, airport is
mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the
sub
table.
Thanks alot,- Hide quoted text -
>- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign
tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have
three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and
helping
others,- Hide quoted text -
>- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -
>- Show quoted text -
Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.
Thank alot- Hide quoted text -
- Show quoted text -

Ok sir,

I again thank you for you help and I will rethink on my database
design and solve the problem.

regards,

farhaad- Hide quoted text -

Apr 24 '07 #12

P: n/a
GH
It might be helpful if you provide specific examples of data you would
enter and how you are currently seeing repetition and blank fields for
Mr. Browne or someone else to better help you. It is not clear from
your descriptions how your data can best be normalized. Examples of
two or three cities with zero or more of your features, including
multiple of a single feature, can help someone else understand your
data structure. Generally when you have multiple tables with many-to-
many relationships, you need an intersection table to resolve each of
these. If your city can have zero or more hospitals, rivers,
airports, trade centers, etc., and each of these individual features
has its own table, then you should expect to see something like the
following table structure:

tblCity (CityID, CityName, etc...)

tblHospital (HospitalID, HospitalName, etc.)
tblCityHospital (CityID, HospitalID)

tblAirport (AirportID, AirportName, etc.)
tblCityAirport (CityID, AirportID)

tblRiver (RiverID, RiverName, etc.)
tblCityAirport (CityID, RiverID)

tblTradeCenter (TradeCenterID, TradeCenterName, etc.)
tblCityAirport (CityID, TradeCenterID)

This type of structure allows a single city to demonstrate NONE of a
feature as well as more than one of one or more features. This also
offers the ability to add other data elements specific to a particular
type of feature (rivers might want length or hospitals and trade
centers number of rooms or capacity). You can also include data
elements specific to the city/feature relationship, such as
tblCityRiver might include the length of river within the city (vice
the total river length, which is in that city's table). This may not
be the structure you need, but maybe it will help you think about the
best way to normalize your data yourself or present examples to the
newsgroup that can better help us help you.

- GH
On Apr 24, 5:40 am, farha...@gmail.com wrote:
On Apr 24, 12:37 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:


Sorry: I'm out of suggestions.
You are closer to your data, and you know what you can and can't achieve. I
can't see it so all I can do is explain the normalized approach. If that
doesn't apply, you can take it from there, and it's time for me to move on
to answering other questions.
All the best
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<farha...@gmail.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
On Apr 24, 10:23 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>No. that is not okay.
>>news:11**********************@r30g2000prh.google groups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>No. That's not right.
>You need a Feature table, with a field like this:
> FeatureID Text primary key
>Then you enter *records* such as:
> Airport
> Tower
> River
>The junction table then has fields like this:
> CityID relates to the primary key of the City table
> FeatureID relates to the primary key of the Feature table.
>So the records in this table look like the example in the previous
>reply.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>No. That's the right structure.
>Ultimately you have 3 tables:
>Table 1 = a list of cities;
>Table 2 = a list of features;
>Table 3 = the junction table between the other two (matching cities
>and
>features.)
>That is the standard way to resolve a many-to-many relation into a
>pair
>of
>one-to-many relations.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>So the junction table will have fields for CityID and Feature,
>and
>you
>will
>have records like this:
> CityID Feature
> ==== ======
> 2 Airport
> 2 River
> 3 Airport
> 3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the
table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no),
airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower)
and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three
choices
in
the feild. here if two cities have airports, airport is
mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the
sub
table.
Thanks alot,- Hide quoted text -
>- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign
tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have
three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and
helping
others,- Hide quoted text -
>- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -
>- Show quoted text -
Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.
Thank alot- Hide quoted text -
- Show quoted text -

Ok sir,

I again thank you for you help and I will rethink on my database
design and solve the problem.

regards,

farhaad- Hide quoted text -

- Show quoted text -

Apr 24 '07 #13

P: n/a

<fa******@gmail.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
On Apr 24, 10:23 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
No. that is not okay.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.<farha...@gmail.comwrote in message

news:11**********************@r30g2000prh.googlegr oups.com...
On Apr 24, 8:42 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
>No. That's not right.
>You need a Feature table, with a field like this:
> FeatureID Text primary key
>Then you enter *records* such as:
> Airport
> Tower
> River
>The junction table then has fields like this:
> CityID relates to the primary key of the City table
> FeatureID relates to the primary key of the Feature table.
>So the records in this table look like the example in the previous
reply.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 4:26 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>No. That's the right structure.
>Ultimately you have 3 tables:
>Table 1 = a list of cities;
>Table 2 = a list of features;
>Table 3 = the junction table between the other two (matching
cities
>and
>features.)
>That is the standard way to resolve a many-to-many relation into a
>pair
>of
>one-to-many relations.
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia
>Tips for Access users -http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
On Apr 23, 9:47 am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
>So the junction table will have fields for CityID and Feature,
and
>you
>will
>have records like this:
> CityID Feature
> ==== ======
> 2 Airport
> 2 River
> 3 Airport
> 3 Tower
>What's wrong with that?
><farha...@gmail.comwrote in message
>>news:11**********************@o5g2000hsb.googleg roups.com...
Dears,
I have many feilds in my tables that are left blank for some
records,
If I make a sub table for it and and change those feilds to a
lookup
feilds so only those options are chosen which fit to the
table,
some
information will be repeated for all records.
Example: I have table for the cities in the country
tblCity(cityID(pk), City(text), river(yes/no),
airport(yes/no),
tower(yes/no)
Here, mayb a city has all the three (river, airport, tower)
and
another city wont have any of them.
So, I made a sub table with a lookup feild and the three
choices
in
the feild. here if two cities have airports, airport is
mentioned
twice in the same subtable.
I hope you got me,
I appreciate your help,
Farhaad- Hide quoted text -
>- Show quoted text -
Sir,
I just want to know if we can avoid the repition of data in the
sub
table.
Thanks alot,- Hide quoted text -
>- Show quoted text -
Thanks alot for the help and Sir!
Can a primary key have many foreing keys? and can these foreign
tables
have different structures depeding on primary keys?
for example, tblCity(CityID, City)1to1 tbl(CityID (fk),
Features(pk)1tomany tblTrade(Features(fk),TradCenters(integer)),
tblAirport(features(fk),
Area(integer),Runways(integer),Terminals(integer))
tblhospital(features(fk),Location, Capacity(ingeger),
Doctors(Integer), Nurses(Integer)).
I hope you got some Idea from the above example, Suppose we have
three
features (Airport,TradeCenter and Hospital) for each city and each
feature has its own attribute so I made seperate tables for each.
I again thank you very much for the help and I hope you help me
with
the above structure if it is ok or should I change it?
I appreciate your extraordinary intelligence in databases and
helping
others,- Hide quoted text -
>- Show quoted text -
Thank you sir,
What I mean here is that each primary key in features table relates to
a different table. For example (airport,city,tower) have their own
tables and foreign keys for the feature table.
is that ok?
I thank you very much- Hide quoted text -
- Show quoted text -

Sir,
Each feature has its own attributes and I cannot bring all the
features in one table because some fields may be left blank for some
features. Pleas guide me what to do.

Thank alot
I think you need to step back and decide exactly what data is really needed.
It looks like you are trying to model the real world, which would require a
very flexible table structure to support almost unlimited attributes. If the
data requirements can be reduced to what is needed and you can live without
perfect normalization i.e. unused fields for certain features, one table
could be used for all features. For example an address table could be setup
to support, Street, PO box, Phone, email, web site, ect. All would use the
"Address" field, only a few would use City, State, Zip. A control code would
identify each address type, forms and code would manage the fields.
Apr 25 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.