database structuring, sql insert multiple tables, help
Posted on 2010-11-08
I'm looking for some brainstorming on database configuration etc. Something isn't clicking. Here's what I'm looking at: I have a table for contacts, tblContacts - PK contactid, fields [FirstName], [MiddleName], [LastName]. I have a table for companies, tblCompanies - PK companyid, fields [CompanyName], [Address]. I have a table for managers of the companies, tblManagers - PK (managerid, FK [companyid] [contactid]). I have a table for directors of the companies, tblDirectors - PK (directorid, FK [companyid] [contactid]), fields [title].
Not every company will have Managers or Directors; however, any company can have several of either. Every contact will be associated with at least one company, but a contact can be a director of the same company more than once so long as the [title] differs.
I have a DetailsView control that displays the information from within the tblDirectors table for a selected Companyid - [title], [Firstname], [Middlename], [Lastname].
If a user would like to add a new Director to the currently selected Company, I would like them to be able to do so via Insert thru the DetailsView control. They would click Insert, enter the [title], [firstname], [middlename], [lastname] fields. The new director's name fields would need to be placed in the tblContacts table as a new record, but the Insert command would then need to discover/use the newly created contactid to Insert a new record into the tblDirectors table, along with the [title] and current Companyid so that this contact is associated with the selected Companyid as a Director. What sort of SQL statements am I looking at creating for this behavior? Is my database structure flawed from the get go? Something really just seems to be off and I can't place it :(
Is this making any sense? I feel like it would be beneficial to have you ask questions and I can then answer them to try and flesh this out. It's not adding up... please ask away.