db design problem... I need a little help...

Results 1 to 6 of 6

Thread: db design problem... I need a little help...

  1. #1
    LeeAnnie Guest

    Default db design problem... I need a little help...

    Okay, this problem gets hairy, so I&#039ll try to be as clear as possible...<BR><BR>I have an intranet site that holds a web application. This application tracks sales people and their sales. We have both internal sales people and external sales agencies that sell our products.<BR><BR>Here are the tables that represent the sales people:<BR><BR>Employees: all employees in the company, including internal sales<BR>Partners: holds all external contacts such as vendors, manufacturers, and OUTSIDE sales<BR><BR>Detailed structure of these tables (leaving out irrelavant columns):<BR>EMPLOYEES <BR>------------<BR>EMP_ID<BR>EMP_FIRST<BR>EMP_LAST<BR>EMP_DEPT<BR >EMP_USERNAME<BR>EMP_PASSWORD<BR><BR><BR>PARTNERS< BR>------------<BR>P_ID<BR>P_FIRST<BR>P_LAST<BR>P_PTYPE<BR>P_USER NAME<BR>P_PASSWORD<BR><BR>We structured these tables to be used for online directories and to hold username and password information. The employees table also holds sensitive data where partners only holds the basics (contact info and login permissions), and a few other columns indicative only to them.<BR><BR>The application also has a table called SALES:<BR><BR>SALES<BR>----------<BR>S_ID<BR>S_TYPE<BR>S_SALESPERSON<BR>.<BR>.<BR>S _WHATEVER<BR><BR>Here&#039s the problem:<BR>HOW DO YOU STORE salespeople from these two tables into one column in the SALES Table? I can&#039t use the ID, because I would have duplicate ID&#039s obviously, and I don&#039t want to store names because people get married and change names, yadda yadda yadda...<BR><BR>Any ideas?<BR><BR>Right now I am exploring the idea of storing ID&#039s from the EMPLOYEE table as "e1109" and then ID&#039s from the PARTNERS table as "p1256".... am I heading in the right direction? <BR><BR>Personally, I don&#039t think so.... that not supporting a one-many relationship and it&#039s rubbing me the wrong way.<BR><BR>Thanks in advance.

  2. #2
    MG Guest

    Default RE: db design problem... I need a little help...

    I am not sure I understand but here is a thought. Explore using multiple columns as your primary key. For your sales table<BR>SALES<BR>----------<BR>S_ID primary<BR>EMP_ID primary<BR>P_ID primary<BR>S_TYPE<BR>S_SALESPERSON<BR>.<BR>.<BR>S_ WHATEVER<BR><BR>Then you will not have duplicates.<BR><BR>

  3. #3
    Join Date
    Dec 1969
    Los Angeles, CA

    Default Not a very good design

    Then you will have null values for either one of these<BR><BR>EMP_ID primary<BR>P_ID primary<BR><BR>I would not think that is a vaery good design. cause for obvious reasons you cant have both the values right

  4. #4
    MG Guest

    Default I am not sure

    I am not sure I understand exactly what she wants to do. Maybe she should have a partner sales table and a salesperson sales table???

  5. #5
    Scott S Guest

    Default RE: db design problem... I need a little help...

    One Quick solution:<BR><BR>the ID which i&#039ll assume is an IDENTITY Field (same as autonumber) Change that to a GUID Globally Unique Identifier something that looks like this: <BR>C801B1B4-A5E8-48AC-A5F8-75DD5390870E the algorithm used to create these is pretty sound (would take thousands of years before you&#039d repeat one) <BR><BR>if you&#039re using SQL Server the NEWID() function will create one for you. If you&#039re using access or something else, you can create them programmatically. Give it a shot, it should solve your problem<BR><BR>Scott S<BR>

  6. #6
    Scott S Guest

    Default RE: I am not sure

    She just needs to use an ID that cannot be repeated by the two tables & her problem is solved, (see my other post about GUIDs) <BR><BR>She could also (IF using SQL Server) use an identity Column on each table, each incrementing by 2 start one at 0 and the other at 1 basically one table would count Odds and the other Evens. Yes its a hack but it gets the job done.<BR><BR>Scott S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts