db-design

Question

Post reply

db-design

swush

SSC Rookie

Points: 41
More actions
June 27, 2002 at 8:13 am

#78695

hi all,
as i'm not a very experienced db-programmer i'd like to hear your opinion on this. i develop web services in php using different dbs. some modules of the same type run several times simultaneously, so that each module has its tables. as hosting with more than one db is often difficult/expensive all tables should reside in one db. therefore they currently have a postfix, which is suboptimal according speed and code-maintenance when i programm my stored procs. how would you do that with sql server?
thanks
jan

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

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

crosspatch Hall of Fame Points: 3168 More actions · Answer 1

Hi Jan

Not quite clear as to your design problem.

Is it that you want a way of identifying database objects that belong to a specific web-service so that your php module can only use those objects designated to it's service?

Perhaps you could give an example of what you're trying to achieve to allow us to focus a bit more

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Agree with crosspatch. The problem is not clear.

Steve Jones

steve@dkranch.net

swush SSC Rookie Points: 41 More actions · Answer 3

ok im trying to be a bit more specific. there are modules which run not only one time but 3 or more times. but each one uses the same data structure. for example the contact module nr1 would use the table contact_1 while the second one would use contact_2 and so on. the _1 or _2 is passed as a parameter to the stored proc. so i always have to use exec('select * from contact'+@par) which i think is bad concerning speed. of course i could add a column for the number and make all modules use the same tables but the table structure is not always as easy as in this example. so what do you think?

crosspatch Hall of Fame Points: 3168 More actions · Answer 4

OK as I understand it so far you need to run the same procedure but have it affect different tables depending on the module number. I suspect you're right about passing in the parameter as you'ld be asking the SQL to bind to the specific table at run-time which I guess means a recompile.

How about building an sp for each table. Then a master sp which will take your number parameter and from that exec the sp that corresponds to the number.

So your module calls the master SP passing in whatever parameters you need plus the id number. The master then execs the sp corresponding to the id passing on any other parameters you want.

So you can build the specific sp's as optimally as you wish and there's no need to rebind the tables at run time

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

swush SSC Rookie Points: 41 More actions · Answer 5

thanks crosspatch,

i got to think about this. i didn't consider this possibility yet. I only hesitate because there may be up to 100 sps per module, so that the number on the whole could easiliy grow beyond 1500 on the whole. but i really like to write sps without exec, because it makes testing much easier.

jan