0

I'm writing a website similar to Amazon. Each user can open a shop.

Thus the database has a Shop table, whose primary key is id. Each user can open at most one shop.

I know I can use Profile Provider. I have been already using it to save the user's address, telephones, and that sort of information. But I think saving shop id to Profile is not a good idea, because I can not use SQL to find out the shop owner's name of a given shop, which makes that I can not use the off-the-shelf data controls without code-behind to complete my function on the shop page.

So can I modify aspnet_Users table adding a shop field which is a foreign key to Shop table? Will it cause problems?

Or I built another User table and put shop field in it?

Or do you have any best practice of doing that sort of things?

Gqqnbig
  • 5,845
  • 10
  • 45
  • 86
  • 2
    Create a relation-table with FKs `ShopID` and `UserID`. You can either use the standard GUID or use an int wrapper. For the latter see my answer on another question: http://stackoverflow.com/questions/6532418/how-to-combine-using-membership-api-with-own-application-related-data/6532611#6532611 – Tim Schmelter Mar 14 '12 at 11:32

1 Answers1

3

Don't touch aspnet_Users. It's managed by ASP.NET's membership provider, it might be changed in the future, lots of views and stored procedures touch it etc.

I recommend saving all profile data in a distinct table(s) and that's including name, address, whatever. That would also include each user's ShopID (as a one-to-one relationship, I think it's preferable, although you can do as @Tim suggested with many-to-many).

Ofer Zelig
  • 17,068
  • 9
  • 59
  • 93
  • Why do not you prefer to use Profile provider in this scenario? Or when will you use it? – Gqqnbig Mar 14 '12 at 12:48
  • I had some bad experience with Profile provider. The default profile provider persists the users' data in an awful, non-queryable way. So if you go in that direction, you should at least implement another profile provider that uses more SQL-ish scheme. For example: http://www.asp.net/web-forms/videos/how-do-i/how-do-i-create-a-custom-profile-provider . But I think it's better to roll your own profile table(s) in which the User Id is taken from ASP.NET Membership provider, and hence it's functioning as a foreign key and not primary key. – Ofer Zelig Mar 14 '12 at 13:04
  • It helped. But I hear the login controls only operate with stored procedures. I have already decided to modify the table and just wait to see if any problems will happen. – Gqqnbig Mar 20 '12 at 10:14