First of all, sorry if this is a question that has been raised before. I’m not sure what it would be called, which makes it difficult to search for.
I am designing a database that will keep track of the contact information for various cable companies that we broadcast our television show through. The basic structure is this (pardon the ASCII Art):
District(1) --------------(many) Cable Company (1)
...........|.\...................................................../.|
...........|...-------------.......................----------...|
...........|...................\..................../................|
...........|................(many).........(many)............|
...........|........................Contact.......................|
...........|...........................(1)...........................|
...........|............................|............................|
...........|............................|............................|
...........+------------------ (many) ------------------+
................................PhoneNumber
A Contact may have many phone numbers (work, home, pager, fax, etc.)
District:
--has many cable companies in it
--may have many contacts
--may have many general numbers (no specific contact)
Cable Company
--belongs to a certain district
--may have many contacts
--may have many general numbers
What I would like to do is have 5 tables, tbl_District, tbl_CableCo, tbl_Contacts, tbl_PhoneNum and setup the relationships to share information, but I run into a problem when in comes to storing the foreign key for contacts and the phone numbers. A record in the phone table could be from a District, Cable Company, or a Contact record. Using one field to store the foreign key would result in collisions/duplicates and there would be now way to distinguish whether the phone number record was for a district, cable company, or contact.
I thought about making separate fields in tbl_PhoneNum for each primary key (of tbl_District, tbl_CableCo and tbl_Contacts). That way if the phone number was for a District, the ‘districtSysNum’ field in tbl_PhoneNum would contain the primary key value from district, and the other two foreign keys would be blank. I’m a little hesitant to do this because it seems like it would be error-prone. I guess I could write code in the forms to force the foreign keys in tbl_phoneNum to be mutually exclusive… But thinking as a computer engineer, having 3+ fields to hold foreign keys, in each record, each using space to hold a Long Integer, when you only need to store one key seems like a waste of memory. I know we’re talking bits for each record, but it will add up with many records. In the past year that I’ve been working with databases, I’ve compacted 30MB of table to less than 20 simply by making string sizes only as long as needed (50 chars for a State name is wasteful when 2 will do) and making numbers sizes a Byte instead of Longs… but I digress.
The other idea, and probably the safest, would be to make separate tables, with the same fields and layout, for each level.
tbl_District
|--tbl_DistrictPhoneNum
|--tbl_DistrictContacts
|--tbl_DistrictContactsPhoneNum
tbl_CableCo
|--tbl_CableCoPhoneNum
|--tbl_CableCoContacts
|--tblCableCoContactsPhoneNum
….
I don’t really like that idea because then instead of 5 tables, you end up with 8 (actually more because I’ve left off several other tables linked to tbl_CableCo, some of which also would need their own contacts and phone numbers as well).
I guess the easiest way to describe what I want to do is to make a table (tbl_PhoneNum) a child of tbl_Contacts and also a child of tbl_District, while at the same time, tbl_Contacts is a child of tbl_District.
Does anybody have any ideas or suggestions on the best way to implement this?
I am designing a database that will keep track of the contact information for various cable companies that we broadcast our television show through. The basic structure is this (pardon the ASCII Art):
District(1) --------------(many) Cable Company (1)
...........|.\...................................................../.|
...........|...-------------.......................----------...|
...........|...................\..................../................|
...........|................(many).........(many)............|
...........|........................Contact.......................|
...........|...........................(1)...........................|
...........|............................|............................|
...........|............................|............................|
...........+------------------ (many) ------------------+
................................PhoneNumber
A Contact may have many phone numbers (work, home, pager, fax, etc.)
District:
--has many cable companies in it
--may have many contacts
--may have many general numbers (no specific contact)
Cable Company
--belongs to a certain district
--may have many contacts
--may have many general numbers
What I would like to do is have 5 tables, tbl_District, tbl_CableCo, tbl_Contacts, tbl_PhoneNum and setup the relationships to share information, but I run into a problem when in comes to storing the foreign key for contacts and the phone numbers. A record in the phone table could be from a District, Cable Company, or a Contact record. Using one field to store the foreign key would result in collisions/duplicates and there would be now way to distinguish whether the phone number record was for a district, cable company, or contact.
I thought about making separate fields in tbl_PhoneNum for each primary key (of tbl_District, tbl_CableCo and tbl_Contacts). That way if the phone number was for a District, the ‘districtSysNum’ field in tbl_PhoneNum would contain the primary key value from district, and the other two foreign keys would be blank. I’m a little hesitant to do this because it seems like it would be error-prone. I guess I could write code in the forms to force the foreign keys in tbl_phoneNum to be mutually exclusive… But thinking as a computer engineer, having 3+ fields to hold foreign keys, in each record, each using space to hold a Long Integer, when you only need to store one key seems like a waste of memory. I know we’re talking bits for each record, but it will add up with many records. In the past year that I’ve been working with databases, I’ve compacted 30MB of table to less than 20 simply by making string sizes only as long as needed (50 chars for a State name is wasteful when 2 will do) and making numbers sizes a Byte instead of Longs… but I digress.
The other idea, and probably the safest, would be to make separate tables, with the same fields and layout, for each level.
tbl_District
|--tbl_DistrictPhoneNum
|--tbl_DistrictContacts
|--tbl_DistrictContactsPhoneNum
tbl_CableCo
|--tbl_CableCoPhoneNum
|--tbl_CableCoContacts
|--tblCableCoContactsPhoneNum
….
I don’t really like that idea because then instead of 5 tables, you end up with 8 (actually more because I’ve left off several other tables linked to tbl_CableCo, some of which also would need their own contacts and phone numbers as well).
I guess the easiest way to describe what I want to do is to make a table (tbl_PhoneNum) a child of tbl_Contacts and also a child of tbl_District, while at the same time, tbl_Contacts is a child of tbl_District.
Does anybody have any ideas or suggestions on the best way to implement this?
