|
DougFord99
Starting Member
10 Posts |
 Posted - 2000-12-31 : 00:57:59
|
| I am building a data entry system using ASP and VBScript to interface a SQL 7 database. I have modeled the database and had it validated by a database designer. Now I need to develop the SQL statements to programatically manipulate the data. I have managed to successfully write a fairly lengthy SProc that inserts data into various tables from an HTML form in one fell swoop, but now I need to get it back out! Ideally I would like to create a SProc that retrieves a particular departments information for a particular customer given the customerid and departmentid. I have written out the tables and their relationships in plain english. From these statements I need to create one or more SELECT statements that retrieves the departmental information for a particular customer given the customerid and departmentid.Tables involved:CustomerCustomer_KeywordDeptDeptTypeDept_DeptTypeDept_KeywordHourKeywordDeptContactDeptContactTypeDeptContact_KeywordPhoneAddressEmailURLWhat is related to the customer? (customerid,company)Dept(deptid,customerid,depttypeid) Keyword(keywordid,keywordtypeid,keyword)What is related to the department? (deptid,depttypeid,customerid)DepartmentType (depttypeid,description)DepartmentContact (contactid,deptid,title,firstname,middlename,lastname,suffix,jobtitle)Address (addressid,deptid,address1,address2,zipid)Hours (hourid,deptid,open,close,title,notes)Keyword(keywordid,keywordtypeid,keyword)Url(urlid,deptid,url)What is related to the departmentcontact? (contactid,deptid,title,firstname,middlename,lastname,suffix,jobtitle)DeptContactType (contacttypeid,description)Phone(phoneid,contactid,phonetypeid,number)Email(emailid,contactid,email)Keyword(keywordid,keywordtypeid,keyword)Customer relationships (customer)1) Each customer can have many departments (dept).2) Each customer can be one partner level (partnerlevel).3) Each customer can have one tagline (keyword).(link table customer_keyword)Department relationships (dept)1) Each department can be many department types (depttype). (link table dept_depttype)2) Each department can have many contacts (deptcontact). 3) Each department can have many addresses (address).4) Each department can have many hours (hour).5) Each department can have one slogan (keyword). (link table dept_keyword)6) Each department can have many urls (url).Department contact relationships. (deptcontact)1) Each contact can be many contact types (contacttype). (link table deptcontact_deptcontacttype)2) Each contact can have many phones (phone) and each phone can one phone type (phonetype).3) Each contact can have many email addresses (email).4) Each contact can have one comment (keyword). (link table deptcontact_keyword)I guess I can write several different SELECT statements to get the data that I need, but I figured that some SQL genius knows a way to programatically get all the information I need using one statement or sproc.Thanks,Doug FordDoug FordMCSE,MCT |
|
HELP - 1st attempt at 4th form normalization