best practices in this case

[lala]

i'm designing a database and there's 2 parts that i have issues with

everything is working, it's just that it feels wrong, like i repeatedly feel that i'm doing the wrong thing, that no database should be designed in this way

it's weird, i can't explain it


1st thing
there are services that they provide and every service has fields that other services don't have. so every service comes with its own set of fields that other services don't have

2nd thing
billing structure for each service is so different that i couodn't find a way to do it other than to do this
if service=1 bill in this way
service=2 bill in that way

both of these bother me a lot. first of all what if they provide some other service with some more fields tomorrow, then i will have to program them in, there's no flexibility.
what do large companies do? this seems so wrong to me that i'm almost sure i'm not getting something, that there's a solution out there that i can't come up with


here's a billling example
if it's service 1 then it's 5% for the first 90 days and 2,5 for any additional 30 days or $750, whichever is larger
if it's service 2 then it's 5% of the amount of goods or $750, whichever is larger

service 3 is 100 bucks a piece


what would you do? for fields i hide and show a huge number of fields based on service selected

for billing i have a billing structure assigned to each service (i came up with 3 billing structures, % for # days or $, whichever is larger, % for additional cycles of #days, % for # items)
also but not as bad as hiding and showing 10000 fields with every selection

 

[DJkarl]

For highly dynamic environments where the rules change frequently what I have done is either create a table or set of tables that contain configuration properties that my code is driven by. I then give users a form to modify or change those properties so if a rule needs redefining, or a new rule needs creation, they can manage that.

If a totally new scenario arises then I may have to change code, but for predicted and expected changes this model has served me well enough thus far.

 

[boblarson]

You are right - it isn't the best structure. For your services, I suggest you take a look at this sample of mine which shows how to have as many different attributes to differing items as you want. And then for the differing calculations, perhaps this sample will show you a way to do that. In that one it is showing a way to have differing fee structures based on levels and you might be able to adapt that idea to replace levels with the services.

 

[lala]

correct me if i'm wrong but you're not changing the rules, you're just changing the values, the billing structure is the same, if more than this and less than this - 5%, if more than this and less than that - %.

in my case i can't even unify it down to this level.
look at my examples

if it's service 1 then it's 5% for the first 90 days and 2,5 for any additional 30 days or $750, whichever is larger
if it's service 2 then it's 5% of the amount of goods or $750, whichever is larger
if it's service 3 is 100 bucks a piece

they have the ability to change %, number of days, $ and all that, it's the STRUCTURE that i'm worrying about. what if tomorrow they come up with a new service where they use some other billling model (i can't even come up with an example)))))

and i'm about to look at your fields example but i think i know what you're doing there, you're using the same box to display different fields. i guess it's a little better than hiding and showing, but let me look at it first before i assume anything

another huge problem that stems out of the different fields is that i'm using continous forms and what can be done then? and i'm going to look at your db now

 

[boblarson]

correct me if i'm wrong but you're not changing the rules, you're just changing the values, the billing structure is the same, if more than this and less than this - 5%, if more than this and less than that - %.

in my case i can't even unify it down to this level.

I didn't say it would fit exactly. It will take some work to get yours to be able to figure out. I would suggest creating rules in parts. So you have a table of rules which you can add and to and then combine. I'll try to think about it a bit, if I have a chance today.

and i'm about to look at your fields example but i think i know what you're doing there, you're using the same box to display different fields. i guess it's a little better than hiding and showing, but let me look at it first before i assume anything

Yes, you shouldn't assume because that's NOT what I'm doing. I'm creating an entity and then I have a table of attributes, which then I have a junction table which joins the particular attributes to a certain entity. So, each entity can have totally different attributes than another and you can be flexible in which ones you add (the attributes would be what you would normally think of as columns). The main entity table would have the fields which would exsist for each entity regardless of who they are.

another huge problem that stems out of the different fields is that i'm using continous forms and what can be done then? and i'm going to look at your db now

Another problem you have is designing from the wrong way out. You need to determine the correct data structure and THEN worry about your forms. You don't design the forms and then the tables to fit.

 

[lala]

i said i was on my way to look at your db)))))))))))) i saw what you did and still thinking about it, trying to see how to implement it for my purposes.

as far as the rates - i'd love to hear what you come up with, this billing is so complicated in terms of unifying it - that i don't know what to do besides literally on a service by service basis. there are also some additional things for each service, llike for the LCs there's the LC amount, the tolerance % and then the resulting amount is the number that all calculations have to be done with.
and there are a few others like that.

and as far as data structure - what happens is that in the process they come up with more rules and ideas. Right now they're totally on paper, so they themselves don't have a good idea of what should happen. i design a little, let them see, they play with it, give me feedback, i fix - and so on. The tables are pretty well designed, in terms of normalization and stuff, the problem is that their company combines 2 businesses that are totally different and that's where the huge difference in fields comes from.