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.
| Author |
Topic |
|
VincentFrandsen
Starting Member
39 Posts |
 Posted - 2007-03-18 : 08:21:01
|
| Hi there guys i'm creating a system. maybe you guys could share som light...Ive got a campaign table with an id, name, ...a transaction table with id, status, compete date, ...a fields table with fieldid,a collection of dynamic tables transactionFields(camapignid) table with id, (fieldid(1),(fieldid(2),(fieldid(3), .......a collection of dynamic tables update(campaignid) table with id, fieldid, old value, new value, ...i have data coming in from xls spreadsheets with 3 upto 200 columns. sometimes similar info sometimes completely different there is no standard whatsoever all my fields are varchar...each excel spreadsheet create a campaign. i need to be able to validate information from each column of incoming data ie if the 37th column needs to be greater than 1000 and less than 50000...I need to track the updates that are made and display them to the user.I origionally created the colums in rows and "pivoted" them to valuate my validation string after getting that to work it was WAY to slow... ive got upto 100 users on a campaign and 1500 users on the group of campaigns. the reponses were taking 9 seconds on just under 1000 records over 35 columns pivoting into rows... there for my biggest campaign of 50000 records with my bigest number of colums 200 would crash the system VERY quickly...Any sugestions would be greatly appreciated...Thanks in advance...Vincent Fradnsen |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-03-18 : 18:50:32
|
quote:
Originally posted by VincentFrandsen
Any sugestions would be greatly appreciated...Vincent Fradnsen
Start again.Learn about normalisation, database design etc. Create a proper data model.Ditch the Excel interface.Stop, step back, think about where you are and where you need to be. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-03-18 : 19:20:09
|
| Good comment LoztInSpace, I like it.rockmoose |
|
|
|
VincentFrandsen
Starting Member
39 Posts |
Posted - 2007-03-19 : 01:11:25
|
| I Agree, but...There is now way to ditch the excel... there is now way to change those fields... they from part of 50 divisions of a bank in South Africa and the current systems (excel docs) come in from the mainframe via a converter that the bank has. each report created on the mainframe over the past 10 years is different... i wish i could normalise the DB. it would make life really easier.Cee my problem...Kindly...(yes it is a good comment...)Vincent Fradnsen |
|
|
|
VincentFrandsen
Starting Member
39 Posts |
Posted - 2007-03-19 : 01:20:09
|
| i origionally wanted to normalize the data but i could have more than the allowed 1012 data columns and as each field is different for numerous camapiagns and a table with three working colums and 1009 non working columns does not seem correct to me. The other problem to normalizeing the db is all fields need to be text / varchar as even telephone numbers are incorrect some have commas and brackets which i can remove granted but others have names appended to the end of say a parent or relative... identity numbers are invalid and contain the incorrect no of characters. the only number available is a account number...How do you normalise that "mess" (i have more descriptive words than mess...)Yet all importantly i do need to be able to use a validation sting and validate data based on account balances first letter in a name or how many ciggaretes he smokes if they send in that column data...Vincent Fradnsen |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-03-19 : 23:25:07
|
| Sounds like there is quite a lot of analysis to be done. To be fair, I doubt you're going to get a full solution on an online forum. For what it's worth though, for normalisation I would start with identifying points of commonality.- Does each branch have a common data feed or does each campaign?- Break the presentation of the data from what it represents- Are there often common rules, or common variations on rules (such as values)- What does a typical campaign "look like"? What varies & what tends not toThese would give you starting points for normalisation.Another thing that strikes me is that you appear to have tied your concept of what the database should look like to what the input looks like. This is not necessarily the case. Just because someone gives you a 50000 column spreadsheet in no way means you are forced to store the data like that.My advice would be to 1) Ignore the input format and look at the data itself2) Try to come up with a data model that is capable of representing your world in a usable form3) Have a go at manually mapping some of your cases to that model. 4) Try some more and repeat until you're ok with it.Don't forget that if you cannot do this then you might as well not have a system. A system is only worth developing if you can present a consistent model, otherwise you're essentially writing one-offs each time. It may be complex, it may be difficult to put your finger on it, but basically you have no hope in hell of being able to design a system until you yourself can understand the situations it needs to handle. I get the impression you are not quite there yet. You need to know where you need to be before you start running around in circles (no offence). |
|
|
|
VincentFrandsen
Starting Member
39 Posts |
Posted - 2007-03-20 : 10:46:14
|
| thanks appreciate the advice but i'm not on a circle i'm on a roundabout... and its to fast to get off? heheheno i haverealized that and i need to do some serious work but thanks for the advice...Vincent Fradnsen |
|
|
|
|
|
|
|
|
Dynamic Tables with backups of updates