Are my tables correct?

[Harold via AccessMonster.com]

Hi, I'm building a database to help track fire sprinkler systems that are out
of service for repairs. I'm just starting and I want to make sure I get the
tables and primary keys correct before I go too far. This is what I have so
far:
tblAddress [AddressID] [StreetNumber] [Direction] [StreetName] 'AddressID
being the primary key
tblBusiness [BusinessName] [ContactName] [ContactPhone]
tblEvent [DateTimeOS] [DateTimeIS] [ReasonOS] [CalledOSBy] [EstTimeTillBackIS]
[Notes] [EventID]

OS and IS stand for out of service and In service. I'm not sure what to use
as a primary key for tblBusiness because there may be several businesses at
the same address and several businesses can have the same name. Should I just
make a BusinessID field for the primary key? Also, should I just include
AddressID in all tables to link them?

Thank you for any help you can give me.

Harold

 

[Guest]

Basically, your structure looks ok

Should I just make a BusinessID field for the primary key?

Yes this is fine, unless you have something else that can uniquely identify
the business.

AddressID in all tables to link them?

Yes. Alternatively, if an event occurs at a specific business, you could
link it to the business (via BusinessID) rather than the address.

It sounds like you've achieved the key thing in setting up a good database -
actually thinking about what you need instead of just creating tables all
over the shop!

 

[Harold via AccessMonster.com]

Thank you very much for responding.
I think that I will link all three tables by AddressID. I would like to have
everything to be connected by the address because somtimes more than one
business can be at the same address (ie. suite # 3). The relations seem to be
the hardest part for me to grasp in understanding how databases work. I've
also heard so much about getting the tables right before you go on to
anything else.

One more question if you can. I would like to have a combo box on a form to
list the street name. To assure that the name gets entered with the correct
spelling. Should I have a separate table with street names for the combo box
to be bound to? This table would have to hold several hundred street names. I
guess it could be connected to the Address table by AddressID also.

Thanks again for your help.

 

[John Vinson]

One more question if you can. I would like to have a combo box on a form to
list the street name. To assure that the name gets entered with the correct
spelling. Should I have a separate table with street names for the combo box
to be bound to? This table would have to hold several hundred street names. I
guess it could be connected to the Address table by AddressID also.

You can have up to 65536 rows in a combo so don't worry about several
hundred streets <g>.

No - the relationship is one street to multiple addresses (all of the
addresses on Main Street for instance). In a one to many relationship
the foreign key goes in the Many side table.

In this case you would use the Streets table as a simple "lookup"
table - it would have probably only one field, the street name (unless
you are dealing with several cities, in which case you might want the
city and the street; this would let you filter the streets for only
those in the selected city). The AddressID would NOT be in this street
table.

John W. Vinson[MVP]