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

 

[Calgary_Neil]

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:

So try with a simple table: (ID )

to mad to even talk

 

[Micron]

I just found on another forum, that a Single Field Table using a autonumber will not increment until a new record is open.

I do understand what you're saying here, and I totally agree. The autonumber doesn't appear until you start to create the record. If you cancel that record, that number won't be available any more. Maybe not important. Besides, an's are NOT to be used as meaningful data. But I don't see what this has to do with your original question. A 1 field table might be OK for certain circumstances, but from what I've gathered of your case, then no. Nor do I agree (yet) that everything should be in one table. I cannot see how that would remotely resemble any level of normalization.

To put it simply, *******er is in Ontario. So is Hamilton. A table with provinces would be one of the entities I wrote about; Cities is another. In tblProvs, ID (if you want it) and ProvName and/or short form:
[TABLE="width: 262"]
<tbody>[TR]
[TD]ID[/TD]
[TD]INIT[/TD]
[TD]PROV[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AB[/TD]
[TD]Alberta[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BC[/TD]
[TD]British Columbia
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MB[/TD]
[TD]Manitoba[/TD]
[/TR]
</tbody>[/TABLE]

tblCities is a related table. Keeping this simple, ID, CityName and how you track which province it's in:
[TABLE="width: 275, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]CityName[/TD]
[TD="width: 107"]PROV_FK[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Calgary[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cold Lake[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Brandon[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Thompson[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]








Hope that helps.

 

[Micron]

CROSS POSTED
http://www.accessforums.net/showthread.php?t=70426

 

[Aladin Akyurek]

@ Micron

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

 

[Kyle123]

FWIW I use a one field table as a number fountain

 

[Aladin Akyurek]

FWIW I use a one field table as a number fountain

Care to specify the database (db) that includes this 'number fountain'?

Here is an example db specification you can use as a template:

EMPLOYEE(EmployeeID, Name, Department, Salary)
EXPERTISE(ExpertiseID, Description)
EXPERIENCE(EmployeeID, ExpertiseID, Years)

where keys are underlined.

 

[Kyle123]

I'm not entirely sure whether your example is aimed at me, I suspect not, or I am completely missing your point.

An example would be where you need an ascending reference number for a record where not all rows have them so one wouldn't use an autonumber, typically when interfacing with systems outside the database.
Another example would be where you need globally unique identifiers, but there are constraints in place that mean that guids aren't a good choice - Sage 200 does this for example.

So these are essentially auto-numbers just from another table, sure you could have more than one field, but the field doesn't relate to anything. Granted, these are not part of a relational design, but single field tables are not without use.

 

[Aladin Akyurek]

I'm not entirely sure whether your example is aimed at me, I suspect not, or I am completely missing your point.
[...]

It was addressing you (also of course indirectly the OP) for you implied to have a db which contains a single field table...

 

[xenou]

So try with a simple table: (ID )

to mad to even talk

From your posts it sounds like you didn't understand how autonumbers work. That's why I suggested a simple table to help. For instance, you would see that its very easy to import data into a table with an autonumber field. I very often test code out in simpler models to understand the concepts, if I am facing something new. I did not mean to offend.

Note that as far as the single field table stuff goes, I'm not understanding. The original post (Post 1 ) mentioned a places table with an autonumber field. If by this we really mean an table with nothing but an autonumber field in it by itself, then its just a list of numbers (1,2,3, .... n), so I'm not sure what that would be used for in this case or how it would help sort out places.

However, in this case at least it is correct to say you couldn't import into that table since technically you would have to import nothing and assign a number to each non-row that was not imported. So I stand corrected!

You could however preload the table with as many numbers as you like and use them as they become needed.

 

[Calgary_Neil]

Now I don’t understand this idea of a ”Number Fountain”, but it sound interesting. My thought on a Single Field Table was to central point to connect items that changed or had a high ratio of nulls. Working this out I noticed that everything moved out from the central point leaving a Single Field Table. Most of the moving out is/was caused by Time (note the To From fields).

[TABLE="width: 50"]
<tbody>[TR]
[TD]tblPlace[/TD]
[/TR]
[TR]
[TD]ID (AutoNumber)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

tblTitle
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID (AutoNumber)[/TD]
[TD]PlaceFK[/TD]
[TD]Formal[/TD]
[TD]Common[/TD]
[TD]Abbv.[/TD]
[TD]Fr
Date[/TD]
[TD]To
Date[/TD]
[TD]Civic
Type FK[/TD]
[TD]Civic Level FK[/TD]
[/TR]
</tbody>[/TABLE]

tblCensus
[TABLE="width: 50"]
<tbody>[TR]
[TD]ID (AutoNumber)[/TD]
[TD]PlaceFK[/TD]
[TD]Population[/TD]
[TD]Year[/TD]
[/TR]
</tbody>[/TABLE]

tblGeoCode
[TABLE="width: 50"]
<tbody>[TR]
[TD]ID (AutoNumber)[/TD]
[TD]PlaceFK[/TD]
[TD]Lng[/TD]
[TD]Lat[/TD]
[/TR]
</tbody>[/TABLE]

TblFunctionary
[TABLE="width: 50"]
<tbody>[TR]
[TD]ID (AutoNumber)[/TD]
[TD]PlaceFK[/TD]
[TD]OfficalName[/TD]
[TD]Position
FK[/TD]
[TD]Fr
Date[/TD]
[TD]To
Date[/TD]
[/TR]
</tbody>[/TABLE]

Now since my data talks in general areas (Counties/etc.) at times I also need to track how they are lumped. Since they (County, City, State, District) almost have the same qualities, they also fit into this structure. Even more so when different countries layer and title differently. This is where the recursion part comes from.

tblLocatedIn
[TABLE="width: 50"]
<tbody>[TR]
[TD]ID (AutoNumber)[/TD]
[TD] LowerFK[/TD]
[TD]UpperFK[/TD]
[TD]Fr
Date[/TD]
[TD]To
Date[/TD]
[/TR]
</tbody>[/TABLE]


I hope this helps

Xenou all I can ask you do, is create a simple table of 1 field which is an autonumber. Now open it and try and increment it.