Database Design

[Jables]

I have been given the responsibility to design a traffic signal inventory for small cities. I have never used Access before, but I know how to write SQL and VBA and I can get around in Access pretty easily.

My problem is that I don't know how to layout the tables and relate them to each other. As we all know, this is half, if not more, of the database design process. So I need a little help. Here's how the information is broken down:

There is data that is specific to an intersection. That is, a primary street and a secondary street define each set of data.

At each intersection, there is the following data:

Traffic Signal Pole hardware --> By each corner of the Intersection.
Operational Information about the hardware -->For whole intersection.
Traffic Signal Controller info -->For Whole Intersection

I need the user to be able to add, delete, and change records and write specific queries using form input (I know how to do this if the tables are related correctly). If anyone could give me some pointers as to how these tables should be declared and related, I would be forever indebted.

Clay Simmons

 

[MichaelRed]

I can only give a brief and very general outline.

The database tables should reflect the physical situation as closely as possible. What ever defines an object in the physical world should define the db table structure in the virtual world. Where ever subsets of things exist in the real world, there should be another table defining the subset object in the virtual (db) world. The subset object should be related to it's parent object via the Key (Primary Key) of the parent object bsing included in the subset (child) object's (Foregin) Key.

A brief example:

The "Intersection" is the highest level object. It is defined by the streets (e.g. 4th St. and Vine St.) so it's Primary Key would be the combination of those fields. Additional info WHICH IS RELAVANT TO THE INTERSECTION would be in other fields in this table.


Additional table(s) would be used for the Items used in the Traffic Control system. An example of this might be the signals. A specific ?Signal? would be at a location (Intersection/warehouse/repairfacility/...) and Identified in some manner (Signal Serial#?). It would have specific info about the actual signal in fields and be related to the Location via the Foregin Key Field which would (in our example) be the intersection (4th St. Vine St.)

Every object in the traffic control system at 4th and Vine would have this "foregin key" in its location column and each table of objects within the system would have a hierarchy of locations to relate to the next higher level object.

To get a BETTER idea of this, get a BOOK on relational data base design. It will explain this MUCH better than I can.

On a different note, the db you appear to be undertaking would seem to be quite an ambitious project even for a moderatly experienced database designer. As a beginner, it may be more than you want to jump into. I am also not sure what size/density area you are including, however MS Access (Actually "Jet&quot may not be the right db engine for the undertaking. I would be quite cautious in accepting this project from what you have posted.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Jables]

Michael,

I want to say thanks for responding. I understand that your response is only general and not too detailed, but I wasn't really looking for detail. What you said about the scope of the project being too much for a novice to handle is exactly the kind of thing I need to hear.

I don't really believe that Access is really right for this application either, but my boss wants it done in Access, so I am giving it a shot. It isn't the size or density of the signal inventory that makes me believe that I'm barking up the wrong tree with Access (the Signal Inventory only includes about 35 intersections), but it's the way in which the client wants the data specified. The only unique piece of data to the whole entire intersection is the intersection itself (that is, the cross streets that identify it). They don't want any serial numbers for the signal heads, or for the controller equipment. They just want to know what kind it is. In fact, most of the data can be specified with check boxes. Things like on which corners the handicap access ramps are installed (check NE, SE, NW, SW). It's my belief that all the data for one intersection could be on one single table. And that makes me think the whole thing could be done virtually hassle free in EXCEL!!!! But, hey, that's management. Thanks again for your help Michael. And I have bought several books on the subject of database design. That's how I came to the conclusion the Access may not be right for this application.

 

[PureCoffee]

Just an idea.

As an option, you could throw everything into an Excel Spread, convert it into an Access Table and then build some nice looking forms based on a query or two. Make it look nice, create a Switchboard etc. When I say, throw it into Excel I am not saying in a careless manner of course.

I had a similar situation and this is what I did for my boss. He loves it.

Steve
sfraser@tay.com