Improve company productivity with a Business Account.Sign Up

x
?
Solved

database structuring, sql insert multiple tables, help

Posted on 2010-11-08
8
Medium Priority
?
307 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:alright
8 Comments
 
LVL 3

Expert Comment

by:paul_p_vargas
ID: 34090511
in my opinion, you only need 3 tables:

1.  Position Table (
                                ID int,
                                PositionName varchar(20)
                                )
ex data: (Manager, Director, Staff)

2.  Company Table (usual company fields, eg. )

3. Contacts Table (ID, CompanyID, PositionID, Title, FirstName, LastName)
                                 
0
 

Author Comment

by:alright
ID: 34090549
Thank you for the input! Problems I see though: each Contact can have multiple positions within the same Company. In addition, while a Manager is always just a Manager, a contact can twice be a Director of a company with two different titles, i.e. titleX and titleY.

John Smith can be Manager of MakeBelieve, Inc but also a Director titleX and Director titleY of PretendCo.
0
 
LVL 3

Assisted Solution

by:paul_p_vargas
paul_p_vargas earned 1000 total points
ID: 34090638
It will still work as 1 user with 2 positions (Manager, Director) will have 2 records in the Contacts Table

1, 1, 1, 'MR', 'JOHN', 'DOE'
1, 1, 2, 'MR', 'JOHN', 'DOE'

where PositionID=1 is Manager and PositionID=2 is Director

if you want to break it apart, you have to create a Person table, which stores only the basic information related to the person (eg. ID, FirstName, FullName)

then in the Contacts Table, you can modify the structure using these fields:
Contacts Table (ID, CompanyID, PositionID, PersonID)

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:alright
ID: 34090652
I like where you're going with this. I'm going to look into that right now but in the meantime I came up with this and wanted to put it on paper while it was fresh in my mind. Could you review this while I'm looking into your idea?

Pardon the crassness, but I think this illustrates what I'm thinking I need to accomplish to achieve the idea of creating a new Contact record and then using the newly created contactid. Does this make sense? Can I refine this code to work or is this not feasible


InsertCommand=
"DECLARE @contactid int, @err int
BEGIN TRAN
INSERT INTO [tblContacts] ([firstname], [middlename], [lastname]) VALUES (@firstname, @middlename, @lastname)
SET @err = @@error
IF @err = 0 BEGIN
SELECT @contactid = tblContacts.contactid WHERE tblContacts.lastname = @lastname AND tblContacts.firstname = @firstname
INSERT INTO [tblDirectors] ([companyid], [contactid], [title]) VALUES (@companyid, @contactid, @title)
SET @err = @@error
END
IF @err = 0
COMMIT TRAN
ELSE
ROLLBACK"
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34091008
0
 
LVL 3

Accepted Solution

by:
paul_p_vargas earned 1000 total points
ID: 34091065
You can use SCOPE_IDENTITY to return the ID generated by the last insert, assuming that tblContacts.ID is your primary key and you set it as  auto-increment.



DECLARE @contactid int

BEGIN TRAN
INSERT INTO [tblContacts] ([firstname], [middlename], [lastname]) VALUES (@firstname, @middlename, @lastname)
Set @contactID = Scope_Identity()
INSERT INTO [tblDirectors] ([companyid], [contactid], [title]) VALUES (@companyid, @contactid, @title)
If @@error = 0
COMMIT TRAN
ELSE
ROLLBACK      
0
 
LVL 11

Expert Comment

by:mattibutt
ID: 34091538
Even the manager has different positions you can still insert them in position two ways to do that if you know they will have just two roles then insert two columns for the position or if its way too many then have two tables first one will have all the position 2nd table can have the actual position I'd with userid and when you fetch them via department they will appear in their domain
0
 

Author Comment

by:alright
ID: 34095642
SCOPE_IDENTITY is working wonders for me at the moment, along with the addition of a tblCompanyContacts table. Thanks so much for the suggestions! Going to leave this question open as I'm sure I may have a few further questions.
0

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question