Many tables vs. one

[LaRetta]

I know that if the data is the same then it should go in the same table. But I've hit a situation where I have (gone ahead and) designed several tables. And now I'm feeling quite guilty and I would like to get a reality check from everyone.

I have a table which acts like a gate-keeper (Router) between a SQL table and our FM solution. All it contains is the SQL unique ID, RouterID, CreationDate, ModificationDate and Status. It's sole purpose is to hold the two pieces together so they never get lost. The RouterID is passed into our solution so we always know exactly which SQL record any given record came from. This Router also tells me NOT to allow exact duplicates, NOT to allow records in if we have chosen to dismiss them from our FM side (or archive them off) etc. Records are updated, added or deleted to keep our FM tables in synch with the SQL data. I cannot import directly into our tables (for many reasons).

Now take that Router table and multiply that 6 times. What I mean is that I have 6 SQL tables I pull into our solution. So I created 6 different Router tables (one for each SQL connection). :

Why? Two reasons: 1) I connect from the Router table to many of our FM tables (for looking up values, correcting SQL data and making it match our requirements before pulling in) and it seemed easier if each Router was a hub by itself with only it's related children (our FM solution) around it and 2) one SQL table only adds 1-2 Router records a day; another adds 1,000.

These Router tables are only for interface (Developer use) and, if someone new took over the design, I thought it would be easier to work with. With the connections separate (which are quite complex), I envisioned down the road how hard it would be to reverse-engineer if it was all combined into one table. Additionally, each business will have different Router connections, ie, some will be giving us imports as flatfile, some SQL pulls. Each Router can be totally different as far as its needs, scripting etc. Some businesses will have 3 of these Routers; others may have 10!

I ask Forums because I feel that I might have self-lied ... it's how *I* think it should be so I come up with the reasons FOR the idea and not against. I worry that I'm blinded by my own opinion of what I think and not because that's how it should be. Thoughts appreciated...

LaRetta

[Colin Keefe]

I personally don't see a problem with the architecture per se. If it were me, I'd be weighing the ease of comprehension your scheme offers against the loss of any shared code opportunities - scripts you're having to duplicate 10 times to point to particular TOs.

It sounds like the scripting is fairly unique to each Router though.

The only other thing that might occur to me is that storing the Router logic and tables in a separate file might keep things in your main file a little cleaner. You could treat your main file as a data file using the separation model, and not need any of your scripting etc there.

That way the Router functionality customizations from business to business never touch the main file. Otherwise I like your thinking.

[LaRetta]

Colin, hi, and thank you for responding. Yes, I should have mentioned that we use the separation model anyway. The data is separate from the UI which is also separate from the Router (external Interfaces) piece. My thinking was that we could work on changing a business' Router more easily.

I just know how it is to go back to something I haven't worked on for 6 months. Because of this, and because of the complexity and individuality of each Router (and business it will attach to), I moved ahead. But still ... it could be ONE table and use different table occurrences. Obviously I still feel uneasy and I hope that Soren doesn't see this thread.

Maybe this is kinda like repeating fields ... if it's only for Developer use then it's okay to break normalization rules. :smile2:

[Colin Keefe]

Well, it couldn't hurt to explore how to make a single table version of the tool work - filtering the data set for a particular router would be pretty straightforward.

What you're worried about I think is having 1000 script variants to manage the various imports and oddnesses of the different business as they get routed through the TOs.

If the table itself only has 4 attributes, though, and you assume that filtering for the right datasets is taken care of...what's the difference from the perspective of scripting, if you're using different TOGs for the different business units anyway?

Put another way: is it any harder or easier to write:

2 scripts using two TOGS pointing to two separate base table anchors, versus

2 scripts using two TOGS pointing to one base table anchor?

One advantage to keeping one table is if you need to add fields t o expand the Router functionality, you only need to add it in your single base table.

Basically, I could be persuaded either way...and it really depends on how functionally similar or different these routers are.

Integration projects are fun, aren't they?

[LaRetta]

Hopefully, Soren will slap me with some hard logic. But yes, these types of projects are fun. I also know that the depth of my knowledge will directly influence how flexible and dynamic this process will be and that's why I'm nervous.

Replacing the base of a structure is the most difficult ... I need it solid and logical before I begin to build so far that turning back would be nightmare. Thank you again and I hope others can give me their thoughts on it as well - many minds are better than one (the Master Mind principle).

[Søren Dyhr]

No I can't becasue - it's not my thing, I feel exactly opposite this...

Integration projects are fun,

...even when the talk falls upon applescript'ing ... too much tinkering!

--sd

[LaRetta]

Ah well, today the entire project came to a standstill. The Primary Key in the largest SQL table - the one I was assured would always be unique per record (which contained the creation timestamp) has changed. The creation timestamp portion of it changed to today's date.

I caught it before importing (while still in my Router and before moving it on) just because I'm being quite fussy. If I hadn't have noticed and would have allowed this data through (which are invoices) then our year-end capital projects would have been WAYYYYY over budget and something would have hit the fan.

Fun? Well Soren, you know I'm a bit sick ... challenges are fun and nothing is more challenging than dealing without non-standard data coming from many different programs from outside sources I can't control. I think I've found my calling.

And yes, I'll be redesigning and using ONE Router table. If my mind keeps questioning over and over then I'm finally beginning to trust that there are reasons behind it (even if very anal and obscure).

LaRetta

[Søren Dyhr]

Ah something really worth memorizing : - I'll direct people right to you the next time I hear of printer issues, this T-shirt here says it all!

--sd

[LaRetta]

I draw the line at hardware (been there, done that) and I don't DO windows printer issues - I'm not TOTALLY crazy, just nuts!