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

 

[mole999]

better to have a unique key, so a two column would be optimal (thats what your autonumber accomplishes)

 

[Calgary_Neil]

I'm I missing something? The autonumber is the unique key, what would be in the other field? The relationships to the other tables are one to many so the autonumber would be the FK in the other tables.
Are you saying make a second unique key .... like YYC15a2?

 

[Micron]

Is a one field table valid and of good design?

Based on what I read, not here. These statements

be located in many different regions (recursive to Places?)
may have a control location which could change (Capital)

indicate one table would not be correct. It may even be problematic going forward.

You need to treat table data according to what's known as "entity relationship". For the sake of simplicity, a "thing" is an entity (e.g. Customer). It has attributes (name, location, phone number, address, etc.). If you haven't read up on normalization, I strongly suggest you do so.

Normalization is important. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/2008/12/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

 

[Calgary_Neil]

Thank you for your reply. The data I'm working with spans 130 years, things change. Kitch ener, ON. was once Berlin ON., Brant county didn't exist, Crysler Farm battlefield is underwater, political ridings boundaries (in ON., three levels), census data boundaries, etc., all change. We (mankind) also have many different systems labeling the same ground with different boundaries (that change).
I grouped District, City, Zone, County, Region, State, and Country into one table because they are all man defined, and have changed over time. Doing a bunch of tables nearly identical felt wrong, especially when most organizations don't use all of them. One thing that is consentient is that is hiericary, and everything (with that time period) had a place. A lot of data is general (a county) in area but some is specific (a town) which (to me) points to the relationship needs a common point to join to (a place).
It is the temporal part that seems to throw me for a loop, and pulls the join table apart. I could be totally wrong (I have been before), but this is why I'm asking.
What "problematic going forward" parts do you see, that I don't? Is there a better way that I'm missing?

Thanks again for the reply
Calgary_Neil

 

[Micron]

the problem part I mentioned means that when data is not properly normalized, it not only can become very difficult to correctly modify (update, delete, append) and might be inefficient as tables can become full of holes (fields with no data in many rows/records, or many fields in a record). You can end up building a form only to find you can't update the info, or build a query to extract data.

Whether or not there is a better way will be primarily up to you. If you read the info at the provided links (no indication that you did) and understand it and still take that position, then I think you know best. If you didn't understand it or how to apply it, there ought to be questions related to your situation. If you didn't read it, then I'm not sure I can help much. I'm happy to point the way and do what I can to help make something make sense, but I don't push anyone. Nor do I "lead anyone to water" as the saying kind of goes.

The relationship you spoke of is probably there (Province to District to County to, City/Town/Village/Hamlet). I see no reason why separate tables wouldn't work even be best but it might be too early for me to be sure yet. One thing that indicates they are is the notion that place names change. Would you rather change every instance of a new town name (that is, everywhere it's used) or just change it in one place and let the relationships propagate the change everywhere automatically? You cannot do that with singular tables.

Enough drivel from me for one day. I'm traveling and have to cut this off for now. Hope some of that helps.

 

[Calgary_Neil]

I just found on another forum, that a Single Field Table using a autonumber will not increment until a new record is open. With one field there is no record to open. So this will not work unless you use code to increment, manually you will need a second field.

I have read the attachments the last time we talked, so either I'm a brick or we are talking past each other (I really hope neither).

 

[xenou]

I just found on another forum, that a Single Field Table using a autonumber will not increment until a new record is open. With one field there is no record to open. So this will not work unless you use code to increment, manually you will need a second field

This isn't clear. You don't open records, you read them (or if you wish, you insert, update, and delete them as well). And autonumbers are quite simple really - when you add a new record, you get a new autonumber value on that row. If you add the autonumber field to the table, it takes care of itself. So there's nothing to worry about when it comes to whether it works or not - if you have one, it will work, and if you don't then it's irrelevant.

For what it's worth, I think you need to give some example of your data. It sounds to me like you want to identify a place based on District, City, Zone, County, Region, State, and Country. In that case, those fields are all your primary key, since they (together) uniquely identify your place. Although it would be an unpleasantly long primary key.

You also want your data to be "temporal". Since things change. But if something changes, (let's say zone), then you seem to want to create new record: Place, with District, City, Zone, County, Region, State, and Country as the key (now a second record, because Zone has changed). THis is all fine, but you also need to think about whether or not there is really any "temporal" aspect here. Since you haven't mentioned anything about storing a date anywhere (i.e., when something has changed).

Let's just say there are are lots of ways to do these things, but not all are equally good, and a few are definitely worse.

 

[Calgary_Neil]

Hmm how to say this... A table of One Field (Type Autonumber) will not increment until a new record is started. Since an Autonumber can’t be edited or entered there is no way of starting a new record. Using code you can, importing I don’t think so. So not a good idea.


PS how to add an Local Image?

 

[xenou]

Hmm how to say this... A table of One Field (Type Autonumber) will not increment until a new record is started. Since an Autonumber can’t be edited or entered there is no way of starting a new record. Using code you can, importing I don’t think so. So not a good idea.

I don't know how important this is. But just to be clear, an autonumber field is automatically updated whenever you start a new record. From the point of view of importing data, you simply ignore it and Access takes care of it.

Let say we have a simple table: (ID, FName, LName) where ID is an autonumber field. You import data from a file like this:

Frodo,Baggins
Sam,Gamgee
Meriadoc,Brandybuck
Peregrin,Took

Now you look in your table and you have all your data with your IDs:

1,Frodo,Baggins
2,Samwise,Gamgee
3,Meriadoc,Brandybuck
4,Peregrin,Took

The same would happen with SQL:

insert into Table1 values ('Bandobras', 'Took')

and you have another row:

5,Bandobras,Took

So much for autonumbers. You should look into it a little more just so you understand. Now on to real problems....