Fixing a BAD db model. >>GOD NEEDED<

Results 1 to 2 of 2

Thread: Fixing a BAD db model. >>GOD NEEDED<

  1. #1

    Default Fixing a BAD db model. >>GOD NEEDED<

    Well, I have managed to find the worst db model EVER and am now working to repair it. It seems the last db person thought ONE table would be fine for everything.<BR><BR>I started spliting the monster table into several smaller tables, but have ran into a snag. What I need to do is figure out how to make a "many to many" relationship into a "one to many". Here is a little sample of what is going on....<BR><BR>OLD TABLE<BR>PRI_KEY MAKE MODEL OWNER OWN_PH#<BR>1111111 ford GL342 Billy 555-157<BR>2222222 ford EIEI7 Billy 555-157<BR>3333333 ford rt432 Billy 555-157<BR>4444444 chev lp989 Jimmy 666-457<BR>5555555 chev 45rtY Jimmy 666-457<BR><BR>THE NEW TABLES (product and owners)<BR>PRODUCT<BR>PRI_KEY MAKE MODEL OWN_PRI<BR>1111111 ford GL342 #NULL##<BR>2222222 ford EIEI7 #NULL##<BR>3333333 ford rt432 #NULL##<BR>4444444 chev lp989 #NULL##<BR>5555555 chev 45rtY #NULL##<BR><BR>OWNERS <BR>OWN_PRI OWNER OWN_PH#<BR>1212121 Billy 555-157<BR>3434343 Jimmy 666-457<BR><BR>I understand that when I do a CREATE TABLE query for owners, that I need to take the primary key for each product in the products table along with (so I can match them up later). That matching them up later is where I am stuck (and trying to get a relationship out of it that can be "enforced".<BR><BR>Someone has to have come accross this before. There are to many people with Access on their desktop and very few good data-modelers...<BR><BR>I am using SQL 7.0 <BR><BR>DRUG_DEALER...<BR>Who still has Access on his desktop.

  2. #2
    Join Date
    Dec 1969

    Default RE: Fixing a BAD db model. >>GOD NEEDED&

    What is the relationship between a product and an owner? What I mean is, can there be products without owners? Is products just a listing of *available* products or is it a listing of "owned" products?<BR><BR>It seems like it is the latter (that each product has an associated owner). In which case what you have now is pretty close. What you want to do to insert the owner id into the new product ID is to join the old product table with the new product table on the Primary Key, then in the same query join the owner table to the old product table by whatever is unique (I assume phone number). Then update the new product tables owner_PH or whatever.<BR><BR>the query will be something like:<BR>Update NewProducts n Inner Join (&#039old Products&#039 o inner join owners w on o.own_ph = w.own_ph) on n.pri_key = o.pri_key set n.own_pri = w.own_pri<BR><BR>If it&#039s the former (a product doesn&#039t need an owner) then you need some additional restructuring yet (products and owners are independant of each other, then some third table, maybe OwnedProducts or something, which relates the two).

Posting Permissions

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