Multi Client DESIGN

Question

Multi Client DESIGN

Surya Prasad

SSC Rookie

Points: 31
More actions
September 21, 2007 at 6:07 am

#198034

I am creating web applicatiopn.
Multiple clients can register for this service and my site pages will be displayed in clients web site (FRAMES..)
Multiple users can register for a client. So every client needs a registration form for users who is registring from his site.
We will provide control panel for every client.
Clients can contol the form fields in the registration form.
In registration form we defined 20 fields.
Client1 selected 15 fields from his control panel. The users who tries to register from this client will see these 15 fields.
Same way Client2 can select 18 fields.
What is the best way of database design to handle this case.
Any ideas are welcome.....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

I am not very clear about multiple users can register a client. Does it mean a client can have multiple users ? What about the control panel - the users who tries to register from this client will see the 15 fields, so does the client define how many field for the user to see?

Maybe if you give a sample of data, it will be clear what you want.

Thanks

tymberwyld SSCertifiable Points: 7810 More actions · Answer 2

You can handle this using 2 design techniques.

The first phase is to use something similar to the ASPNET Membership Provider database. Although the base implmentation probably won't suit your needs (as it did not mine), you can easily create your own MembershipProvider implementation in under a week.

The second phase is setting up the Registration design. You can easily accomplish this by using an EAV Model (Entity-Attribute-Value). I would never limit a client to having only 20 "pre-defined" fields. With an EAV model in place, the client can easily define their own Registration "attributes" as well as any lookups needed for them. The Registration design only needs 2 tables and the Lookup structure only needs 2 tables as well. So, for your complete Registration process, you can do everything with 4 tables (aside from however many tables you're going to need for the Membership side).

I could probably design this structure in about an hour. I'm not sure how I could post it though...

Surya Prasad SSC Rookie Points: 31 More actions · Answer 3

Second option seems better. I have started working on this. While displaying how to transform rows to columns and how to write insert stored procedure to insert fields dynamically.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 4

The second option gets more confusing for data manipulation. I like it better, but you have to understand the 21 values in the registration form will be rows in a table, not columns.

So you'd have to pull back all data from person X, for client Y, to display the data and iterate through a 21 row record set to display the data.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 5

Steve Jones - Editor (9/27/2007)
The second option gets more confusing for data manipulation. I like it better, but you have to understand the 21 values in the registration form will be rows in a table, not columns.
So you'd have to pull back all data from person X, for client Y, to display the data and iterate through a 21 row record set to display the data.

There's no need to write a procedure for each "set" of registration options. This can be done with one procedure (usp_SaveRegistrationValues?). I've done this before using "Events" where there was no telling what attributes were going to be stored. I used 1 procedure that had a few basic parameters and one Xml (or Text in SQL 2000) parameter that would get parsed and insert all the values in the Xml as attributes. It was set-based (no looping on the Xml) so storing the values was fast and efficient.

You can bypass the "looping" one of two ways. a) wire up a grid control which has two columns: Attribute & Value. The User won't see the Column headings, just the fields they need to enter. I used to do this easily with DevExpress's Grid Controls. Or b) Write wrapper views which pivot the attributes into columns. I did this for my "Events" but that was because we knew what data needed to display. However, since this is dynamic, the grid is probably the best choice. You could also consider a DataRepeater which would work beautifully.

As far as data retrival goes...Select * From RegistrationValues Where CustomerID = ?. Not a biggie.

CustomerID AttributeID

1 1 (FirstName)

1 2 (LastName)

1 3 (DOB)

2 1

2 2

3 4 (Email)

3 4 (SSN)

Tom Zeppenfeldt Newbie Points: 3 More actions · Answer 6

Perhaps this may be helpful..

We have developed an app on top of MSSQL that uses the EAV. It allows users to create entities and attributes freely in a browser app and has quite a lot of querying possibilities, from simple Query By Example like stuff, as well as more advanced multi-entity queries, for which the app handles the transcription from a select as it would be written against a non-generic model to a select against the EAV model.

on http://www.trydatheon.com you find our sandbox.. after registration, open the "tree" menu and look for the manual.

In our experience, this app (and any other EAV tooling that has a GUI) is extremely useful for prototyping database apps, and for environments where flexibility is more important than performance, even useful as a production environment. Ourselves we use it for all our data-driven apps like CRM, Document Management as well as Trip registration and CMS.