 | |
#11
 
| |||
| |||
|
Re: mysql column limits? -
02-28-2011
, 11:14 AM
|
Root is Country - say USA next is a table of states each with a unique ID and name that may have a field to indicate which country they are in. then comes a series of counties, whose name may not be unique, but each one has a unique ID and a 'state' field that indicates which state they belong to....and so on down to town, street, ... |
with such solution...or at least with the assumptions.
Is it really true, that towns are always part of a county, which are
always part of a state - in _every_ country you may think of?
[Hint: the answer is no. In some countries, the structure is really
complicated. These may have stateless cities, or towns that are part of
a bigger city. Germany would be an example.]
--
Erick
#12
| |||
| |||
|
|
On Mon, 28 Feb 2011 11:58:50 GMT, Doug Miller wrote: In article <1i5bcl39yj1m$.dlg (AT) evanplatt (DOT) sux>, richard <member (AT) newsguy (DOT) com wrote: Is there a limit to the number of columns a table can have ? Asking this question indicates a (probably) ill-thought-out table design. Why do you believe that you *need* to have so many columns in one table that you might encounter some limit? Let's say I wanted a table with 365 columns. One for each day of the year. |
Instead of
<SomeKeyField> <day1value> <day2value> ... <day365value>
it makes much more sense to do this:
<SomeKeyField> <dayNum> <value>
Quote:
|
Or maybe I want a table where column 1 is the state name, then the remaining columns is for each city and county. If the column limit was only two hundred, I'd have to reinvent my table wouldn't I? |
table. So start with a better design, and you won't have to re-invent it.
#13
| |||
| |||
|
|
Let's say I wanted a table with 365 columns. One for each day of the year. |
Possibly a third for a unique primary key. Depends on what the table is
actually for. Describe your specifications for a complete answer.
Quote:
|
Or maybe I want a table where column 1 is the state name, then the remaining columns is for each city and county. |
1 - primary key, state
2 - primary key, foreign key of state, county
3 - primary key, foreign key of county, city
Quote:
|
If the column limit was only two hundred, I'd have to reinvent my table wouldn't I? |
Become friendly with Dr. Codd's rules.
--
-bts
-3NF was always good enough for my work
#14
| |||
| |||
|
|
The Natural Philosopher: Root is Country - say USA next is a table of states each with a unique ID and name that may have a field to indicate which country they are in. then comes a series of counties, whose name may not be unique, but each one has a unique ID and a 'state' field that indicates which state they belong to....and so on down to town, street, ... While I agree with the general assessment, I'd still be very careful with such solution...or at least with the assumptions. Is it really true, that towns are always part of a county, which are always part of a state - in _every_ country you may think of? Wel this was USA specific. :-) |
Quote:
|
[Hint: the answer is no. In some countries, the structure is really complicated. These may have stateless cities, or towns that are part of a bigger city. Germany would be an example.] |
countries, then counties, with no states to speak of. Although we do
talk of 'regions'
So I agree sucn an approach is universally valid conceptually, but not
in specific naming of the entities.
I dont think Germany has cities containing towns though.
Quote:
#15
| |||
| |||
|
|
Wel this was USA specific. :-) |
Quote:
|
[Hint: the answer is no. In some countries, the structure is really complicated. These may have stateless cities, or towns that are part of a bigger city. Germany would be an example.] Germany has regions, like bavaria.. |
government. Germany is a federation.
Quote:
|
So I agree sucn an approach is universally valid conceptually, but not in specific naming of the entities. I dont think Germany has cities containing towns though. |
- Länder
- Regierungsbezirke
- Kreise
- Samtgemeinden, Märkte, etc.
- Gemeinden
Not all levels are present everywhere. Not every 'Land' has
'Regierungsbezirke', but some do. Then you have 'kreisfreie Städte'.
Most of the levels are identified using the AGS (formerly GKZ, a key
that consists of numbers for every level), but the level of
Samtgemeinde is excempted from that, which makes building database
systems for German community hierarchies quite painful.
--
Erick
#16
| |||
| |||
|
|
The Natural Philosopher: Wel this was USA specific. :-) Sounded like USA specific, yes. [Hint: the answer is no. In some countries, the structure is really complicated. These may have stateless cities, or towns that are part of a bigger city. Germany would be an example.] Germany has regions, like bavaria.. In Germany, these are "Länder", much like states with their own government. Germany is a federation. So I agree sucn an approach is universally valid conceptually, but not in specific naming of the entities. I dont think Germany has cities containing towns though. Germany has several levels below 'country': - Länder - Regierungsbezirke - Kreise - Samtgemeinden, Märkte, etc. - Gemeinden Not all levels are present everywhere. Not every 'Land' has 'Regierungsbezirke', but some do. Then you have 'kreisfreie Städte'. Most of the levels are identified using the AGS (formerly GKZ, a key that consists of numbers for every level), but the level of Samtgemeinde is excempted from that, which makes building database systems for German community hierarchies quite painful. As does littering their towns with streets named 'EingangStrasse' - |
#17
| |||
| |||
|
|
As does littering their towns with streets named 'EingangStrasse' - often several in the same town :-) :-) |
(It's Einbahnstraße, but what the heck...)
Yup, just like Sackgasse. Plenty of 'm!
#18
| |||
| |||
|
|
richard the sto0pid wrote: Let's say I wanted a table with 365 columns. One for each day of the year. Two (or three) columns. 1 for the content, 1 for the day of year. Possibly a third for a unique primary key. Depends on what the table is actually for. Describe your specifications for a complete answer. Or maybe I want a table where column 1 is the state name, then the remaining columns is for each city and county. Three tables. 1 - primary key, state 2 - primary key, foreign key of state, county 3 - primary key, foreign key of county, city If the column limit was only two hundred, I'd have to reinvent my table wouldn't I? No. You need to learn database design. Google for "Third Normal Form." Become friendly with Dr. Codd's rules. |
way.
#19
| |||
| |||
|
|
Beauregard T. Shagnasty wrote: richard the sto0pid wrote: Let's say I wanted a table with 365 columns. One for each day of the year. Two (or three) columns. 1 for the content, 1 for the day of year. Possibly a third for a unique primary key. Depends on what the table is actually for. Describe your specifications for a complete answer. Or maybe I want a table where column 1 is the state name, then the remaining columns is for each city and county. Three tables. 1 - primary key, state 2 - primary key, foreign key of state, county 3 - primary key, foreign key of county, city If the column limit was only two hundred, I'd have to reinvent my table wouldn't I? No. You need to learn database design. Google for "Third Normal Form." Become friendly with Dr. Codd's rules. I was only using that scenario as an example. Not that I would do it that way. |
of ever doing anything like this" so the rest of us can just pass on by.
Google for: tutorial relational database theory
--
-bts
-Four wheels carry the body; two wheels move the soul
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.