DDL thoughts for preset yet customizable data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DDL thoughts for preset yet customizable data?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-27 : 02:30:27

Okay, I'm a bit stumped on how to implement a requirement of a project I'm currently working on. One example is defined as such...

The ability must be present for a preset fee structure to be assigned to a user dependent on the package the customer is signing up for. Some customer's may get special pricing. In this case a set of preset fees should be able to be chosen, with some or all of the fees being able to be customized. The option must also be present to use no default values, and specify a completely custom fee pricing structure.

If a customer is assigned fees from a preset structure, and the assigned preset fee structure is changed, then any of the customers' fees that do not have custom values should be updated to reflect the new fee structure.

In addition, some fees can be altered depending on different criteria, such that the actual fee is more of a base fee that can fluctuate. These would have to be presettable as well. The following would be examples of this. If x number of articles are purchased, then the per article price is lowered. If the customer refers n new customers, then the monthly fee is lowered, or waived.

Do any of you guys have any thoughts on this? I'd like to make this as easy as possible for myself to maintain, as well as query.

Here's what come up with so far:

IDEA 1:


CREATE TABLE PresetFees (
	PresetID int NOT NULL,
	PresetName varchar(50) NOT NULL,
	SetupFee int NOT NULL,
	MonthlyFee int NOT NULL,
	ArticleFee int NOT NULL
)

CREATE TABLE PresetArticleModifiers (
	ModifierID int NOT NULL,
	PresetID int NOT NULL,
	PurchaseThreshold int NOT NULL,
	RateModifier int NOT NULL
)

CREATE TABLE CustomerFees (
	CustomerID int NOT NULL,
	PresetID int NOT NULL,
	SetupFee int NOT NULL,
	MonthlyFee int NOT NULL,
	ArticleFee int NOT NULL	
)

CREATE TABLE CustomerArticleModifiers (
	CustomerID int NOT NULL,
	ModifierID int NOT NULL,
	PurchaseThreshold int,
	RateModifier int
)

This seems like it would be very high maintenance if a new fee was ever added. Also, this whole business of presets, and overrides happens many more times throughout the system. I want to figure out the best way to approach this for all instances, especially since some of the presets in question would be very large tables. The CustomerFees table would allow nulls for the values, since it would seem logical that if it referenced some preset fee structure, and did not override any fees, then there is really nothing there. Although I could possibly come up with some default "known null" value to stick in there as well.

IDEA 2:


CREATE TABLE Fees (
	FeeStructureID int NOT NULL,
	SetupFee int,
	MonthlyFee int,
	ArticleFee int,
	IsPreset bit NOT NULL
)

CREATE TABLE ArticleModifiers (
	ModifierID int NOT NULL,
	FeeStructureID int NOT NULL,
	PurchaseThreshold int,
	RateModifier int
)

CREATE TABLE CustomerFees (
	CustomerID int NOT NULL,
	FeeStructureID int NOT NULL
)

This one seems a little better in that if a customer uses a preset, then it is referenced in the CustomerFees table. If custom fees are needed, then an entry could be made in Fees overriding the necessary values, and then referenced in CustomerFees. If no preset is wanted, then an entry could be placed in Fees, and referenced in CustomerFees. It might be a little more manageable, but still could be a little bit of a pain if a lot more settable values are added. As mentioned above, some of the tables in question could get pretty long this way.

Instead of having the junction table, everything could be in the Fees table, and it could self join on a new column named ReferencedFeeStructureID or some such. Any presets would be assigned to a dummy CustomerID or some such.

IDEA 3:
Either one of the above, except using key/value pairs instead of hardcoding the fees. Modifier tables would still need to be created and mapped somehow when needed. The upside is that it wouldn't matter how many fees/settable values are added in the future, it's just another key/value pair. The downside is that everything ends up being a varchar, and queries can end up ugly when modifier tables need to be used.

Any thoughts or ideas on the above would be appreciated. Upsides, downsides, or even if you guys have some different ideas.

Thanks,
Steve

Kristen
Test

22859 Posts

Posted - 2004-10-27 : 03:33:27

Dunno if its relevant, but as a thought:

I tend to use a special sort order to find what the appropriate fee is - given a "business rule"

So it might be


SELECT TOP 1 Fee
FROM
(
    SELECT FeeRate as Fee,
           1 as Relevance
    FROM   ClientTable
    WHERE  ClientCode = 'FRED123'
    UNION ALL
    SELECT JobFee,
           2 as Relevance
    FROM   JobFeesTable
    WHERE  JobCode = 123
    UNION ALL
    SELECT GeneralFee,
           3 as Relevance
    FROM   GeneralFeesTable
    WHERE  FeeCode = 'ABC'
) T
ORDER BY Relevance

Kristen

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-27 : 19:31:34

That seems like it would be terribly messy in that you hardcode all of these values(I would assume one for each fee) in your stored procedures. I might be missing something though.

Kristen
Test

22859 Posts

Posted - 2004-10-27 : 20:35:28

The red bits are intended to be parameters.

Basically my example is for the Business Rules of: ClientTable FeeRate if there is one, otherwise a JobFeesTable JobFee if there is one, otherwise a GeneralFeesTable GeneralFee

So I would maintain three sets of Fees tables, and whichever one, in Ranking order, has a matching fee would be used.

Kristen

Blastrix
Posting Yak Master

208 Posts

Posted - 2004-10-28 : 12:34:53

Does anyone else out there have any ideas on this? Surely someone has had to implement a situation such as this.

Thanks,
Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources