| Author |
Topic |
|
darenkov
Yak Posting Veteran
90 Posts |
 Posted - 2005-01-23 : 09:05:10
|
| I've created three relational tables so that users can create multiple businesses (for a free business directory) from the one login account:LOGIN TABLElogin_id (PK)email_addresspasswordBUSINESSbusiness_id (PK)business_nameaddress ..... etcLOGINBUSINESSlogin_id (FK)business_id (FK)My problem is that even though I have created foreign keys on the lookup table and specified to ENFORCE RELATIONSHIP FOR INSERTS AND UPDATES, and also CASCADE UPDATE RELATED FIELDS, the records aren't automatically inserted into the LOGINBUSINESS lookup table when I add them to the LOGIN table and the BUSINESS table. I am pretty new to this so am not sure if I am doing something wrong, or if I should abandon this method and just use a function or a trigger to do this?! Any ideas? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-23 : 10:14:31
|
| What are you expected to be added to the LOGINBUSINESS table?How do you expect te server to know know which businesses to associate with which logins?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jhermiz
3564 Posts |
Posted - 2005-01-23 : 14:13:14
|
There is nothing wrong with your structure here darenkov, however SQL Server cannot tell you which login goes to which business entity as nr pointed out. Where you really expecting a database tool such as SQL or Oracle or Access or anything for that matter to automatically insert a login to a business entity ?Your table structure is fine, now just create a user interface that allows someone to select their login and their business entity they want created. Then do an INSERT into the LOGINBUSINESS table.Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
darenkov
Yak Posting Veteran
90 Posts |
Posted - 2005-01-24 : 01:26:43
|
| The login knows which business it is because the LOGINBUSINESS table has the business_id and the login_id fields which are common to both tables. I was hoping that when I inserted data into the LOGIN and BUSINESS tables respectively that it would have automatically been added to the LOGINBUSINESS table, as they are linked by foreign key, and I have selected the ENFORCE RELATIONSHIP FOR INSERTS AND UPDATES checkbox on SQL Server 2000 enterprise manager console. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-24 : 06:12:48
|
| You mean you want all login's associated with all businesses?i.e. when you insert a login records get added to LOGINBUSINESS associating with all businesses and similarly for busineses?Maybe there's no point in having the LOGINBUSINESS table in that case.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
darenkov
Yak Posting Veteran
90 Posts |
Posted - 2005-01-24 : 06:25:33
|
| the tables i have been referring to belong to a online business directory system. i want to allow a person to sign-up with their email address and add their business. however i want them to be able to come back in the future and add a second or third business if they have one; but to use the same login to do this. that is why i created the LOGINBUSINESS table, so that 1 login account can have multiple businesses associated to it. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-24 : 06:59:43
|
| OK, but how do you tell sql server which login to associate the business with when it is added to the business table?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
darenkov
Yak Posting Veteran
90 Posts |
Posted - 2005-01-24 : 07:07:26
|
| nr, i must be doing something wrong. can you make a suggestion as to a better way to do it? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-24 : 07:22:44
|
| Call a stored proc with the login and business.Insert the login if it is not thereInsert the business if it is not thereinsert the link into the LOGINBUSINESS table if it is not there.Do it all in a transaction.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
darenkov
Yak Posting Veteran
90 Posts |
Posted - 2005-01-24 : 07:45:10
|
| ok thanks, back to the learning board for me. any ideas where i can find good tutorials on creating transactions? |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-01-24 : 10:40:36
|
quote:
Originally posted by darenkov
ok thanks, back to the learning board for me. any ideas where i can find good tutorials on creating transactions?
Daren,Before you go any further, you probably should reread your post and understand this a bit more. It almost seems like you assumed that adding a login and a business automatically gets added to this inner table. But how do you expect that????? How does sql server or any relational database understand that when you add a login and you add a business that it automatically links the two.My suggestion is after you realize that is not possible to think of a user interface to do this. First if you are adding a login and a business at the same time and you want these linked you can do an INSERT into a login table (make sure the user doesnt exist already). After that, you insert the business (make sure the business doesnt exist again??). So once these are done you take the ID of both entities and store them into this other table you have created.You should store this in one single transaction, as nr already pointed out, only when new user / businesses join. For members who come back to the site your user interface should not expect them to create a login. You should take them to a page that allows them to simply enter another business. Then in this case you already have the loginid, so you pass it to the procedure which relates the login to the business.Make sense ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
darenkov
Yak Posting Veteran
90 Posts |
Posted - 2005-01-24 : 11:02:40
|
| thanks for clearing that up |
|
|
|
|
LOOKUP Tables