Database Design, One field table

[Calgary_Neil]

Hi. I have been fighting with myself over this design question, Is a one field table valid and of good design?

I'm trying to create a database which records locations though time. So for a "place" it it will have
a name (one or more)
a population (zero or more census)
a location (GeoCoded maybe, an address maybe)
be located in many different regions (recursive to Places?)
may have a control location which could change (Capital)

So in my mind I see the Places table as a single field table using a autonumber, with relationships to these tables which would have a To/From date fields.

Now I'm also thinking that there is no difference between District, City, Zone, County, Region, State, and Country so that would be all recursive. (note some GeCodes would be to give a general area)

So does this sound right???

Calgary_Neil

 

[xenou]

I think if you want a single table field put a second field in it. You can even leave it blank if you want. Then it will have two fields but you will only need to use the one field that you really want. You can create a new record in that table simply by inserting a new record.

table definition:

Places ([ID] autonumber, [X] text)

sample inserts:

insert into Places ([X]) Values ('')

or

insert into Places ([X]) Values (null)

I don't think you have actually read last my post since you are asking me to try something that I literally admitted that I stand corrected on. That said, you can just use a number field instead of an autonumber field.

 

[Calgary_Neil]

xenou I'm sorry. I did read that you agreed about the uploading, but didn't see that you had actually tried it.

Now what you suggest is what I have done on the other side of this design, because I was confused if the right record was 41 or 42 without some type of check. (Vlookup)

Is there an easy way of showing you the ER digram out of Access? Insert Image wants a external link.

As micron point out that I'm close to where I was 2 years ago (eech), and still don't want to create a data train wreck. (Big pile of ). The way you all are saying bad design, makes me think there is a better way, but I'm not seeing it. Since the data isn't used (now) in a process, and I'm the only one in this project, the rules are bendable. I just currently I just want to a report similar to one of the inputs from about 6 different areas.

Having done COBOL on a RDBMS (PO-Materials-Costing) I think I know (very dangerous) if I can those reports out. What I don't know is where access will choke, and how to properly design a DB.

I know what I'm asking for is not simple Yes/No answer, and could take a long time to work out, but I do have the time and engery to work this out.

Thanks again zenou and mircon

 

[Micron]

@ Micron

I'm curious though what use a single field table would fulfill in a well-formed relational database!..

I didn't say that. My comment was about the autonumber not appearing until record creation is started. I expounded on that in the next sentence.

 

[xenou]

So here's a question to bake your noodle: when would you use your one-field table of numbers in an actual query?

 

[Calgary_Neil]

Xenou, if that’s to me, I don’t know. You asking gives a hint it would be confusing and near impossible.

 

[xenou]

No, not confusing or near impossible (trivial examples would be extremely easy to find - just select anything from any table and join it to this one). I;m just wondering what the use of this one-field table would be in actual queries. I haven't thought it over a lot but so far I don't see a use for it, which suggests to me it's not a table you need. If you agree then that's one step forward - we can take that idea off the table.

 

[Calgary_Neil]

Back in message 20 I sketch a one filed table with autonumber called tblPlace. I now know that using autonumber in access this way is wrong, but I keep coming up with a star design with a small central table. I even played with using the tblPlace and adding the common name from tblTitle but have to add something for places like London (ON, vs England), just to keep it clear to me.

 

[xenou]

That's fine. I'm just not seeing a real reason to ever use a one field (autonumber or number) table in a real query. All it would do is match foreign keys that are in other tables, and it wouldn't have any other information to add, and wouldn't help create any joins either. In short, no real reason for it. A different table may actually be a candidate for your "central" table.

 

[Calgary_Neil]

Hey I just an engineer, you know always using the same old answers. I have tried different central tables but the same effect happens. I may need a different approach to the data (Time central ?), but I see more flaws and holes and mind bends, but ...

The information is in the relationship, 3 city titles, 5 counties, 2 countries, lots of mayors over time all below to the same physical place (or area). GeoCodes have been but I don’t have them all (just a few, 1%) and lng-lat doesn’t ring bells with (minor point)

Do you need me to populate the table in 20?

 

[xenou]

all that is fine. I'm just saying write a query and do it with/without the one field table - I think the results are the same. Which suggests the table is unnecessary. By the way, how do you distinguish London England from London Ontario in your tables?