Need help designing traffic sign inventory DB

[fredep57]

I am in a small city and we now have to have some mechanism to account for our signs - all signs in the city control.



So.... I have tried to figure out a design schema, but am getting frustrated that I can't seem to get it even close.

What we need to do is

• account for each sign
  
  • what type
  • when purchased
  • who purchased from
  • cost of sign
  • type of facing (new or refurb)
• account for where and when it was put in the field
  
  • date installed
  • post installed on
  • location of installation
  • direction sign in facing
  • GPS coordinates
  • date removed (make it inactive?? or ??)
• account for signs not yet installed (in shop)

I don't want to delete any records - for historical data collection - and would like to make some kind of an active/inactive file/table(?) to keep track of these.

So, I guess I just need some help with this.

Thanks
Ep

[alansidman]

Based upon the information you provided, I envision five tables as follows:

Placement Table
----------------
PlaceID (PrimaryKey)
TypeSign (Foreign Key)
VendorID (FK)
DatePurchased
Cost
FacingID (FK)
Location
FaceDirection (FK)
InstallDate
RemoveDate
PostInstallDate

Type Table
-----------
TypeID (PK)
TypeDescription

Vendor Table
-------------
VendorID (PK)
VendorDescription
OtherVendorFieldsAsRequired

Facing Table
------------
FaceID (PK)
FaceDescription

Direction Table
--------------
DirID (PK)
Direction



If your Install date is Null, then your query would indicate it is still in the shop awaiting installation. If your Date Removed field is null, then the sign is still active.

Good luck with this. You may want to read the following to help understand data base setup and normalization
http://www.deeptraining.com/litwin/d...aseDesign.aspx

Post back with questions as you progress.

Alan

[fredep57]

Thanks, Alansidman. This will help me get a better understanding of databases and tables and such.

However, I have also been told that each sign has to be unique. That means that it is serialized so that each sign has a 3 letter prefix for the sign type followed by a dash and 4 digits (for the year) followed by a dash and 8 digits (for serialization).

Also, each sign might go in and out many times, also be "referburished" many times and/or could be lost or scrapped. All this information needs to be captured and retained for historical data collection (used to see what and when to buy more of that type of sign and, in the case of lost/stolen, to retrieve it in case it "shows up" again).

Make sense?? Now you see my dilemma in trying to get my head around this since I am not a DBA type person.

Thanks again
Ep

[alansidman]

For your additional information you will either add that information to existing tables or create new tables as needed. Make sure to read the information on normalization and try it out. If you encounter difficulties, post back with specific issues in new threads. There are lots of people in this forum anxious to help and teach you what they know. Good luck.