Concurrency Access 2007: Some Design & Implementation Questions

[boblarson]

Well, first of all, it is a good idea to split it BEFORE you add data. It should only be done once.

Second, how did you split it?

 

[flyinghippo99]

Well, first of all, it is a good idea to split it BEFORE you add data. It should only be done once.

Second, how did you split it?

I just went to Database Tools menu and did a split. I closed all forms and tables. After the split, I looked at the FE db and most of the tables are LINKED to the BE. But the file size is IDENTICAL...

Strnage..

 

[boblarson]

Even after running Compact and Repair on both?

 

[flyinghippo99]

Even after running Compact and Repair on both?

I take it back. The BE is SMALLER than the FE. HOWEVER, the FE is the SAME SIZE as the original unsplitted db file. Hmm... I thought after the split the FE should be smaller than the original size..

 

[flyinghippo99]

Bob et al,

The source of the complexity is this. I might as well lay it out here since this is afterall the Theory and practice of db design forum. So, here it goes:

1) I got Fieldname that needs to have heterogenous data values. For example, Field1 is usually
numeric("Standard" as Access would call it) but every once in a while it needs to show "na" or some other alphanumeric designations. Now, we are all know that's it's NOT possible to mix data types in Access for a particular field.

2) My workaround is to create 2 tables for each Field: Table1 and Table1NA. Now, you can see why it runs into the hundreds of tables quickly(hehe). BTW, the Key in both tables is months (mm/1/yyyy). Table1 has all the field values that are numeric. Table1NA has the "na" string in the months where the Field1 is supposed to be "na" and if a particular month has values then there will no "na" string for that month. Table1, on the other hand, has all the Field1 values but for month(s) where it doesn't have a value that PARTICULAR MONTH will NOT be present in Table1. You'll see why in a minute.

So, to do view on various Fields, I ended up create hundreds of these slim table pairs(Table1,Table1NA, Table2,Table2NA,..,TableKth, TableKthNA). Then I do a supernasty SQL query to combine these into one view. I know it's a complex design. But it was the only way I could display both "na" and the Field values.

And this is probably where all the overhead and bloating are happening. Any ideas?

thanks!

flyinghippo99

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=74

 

[boblarson]

The real workaround is to just use a text field for both.

 

[flyinghippo99]

The real workaround is to just use a text field for both.

Yea, I thought about that from Day 1. HOWEVER, the Data Entry forms, the user
reqs is that they can enter the data in their original format($, %, text, decimals, integers,etc.) Ack. I guess I could let them enter into a local table with the right data types then do a conversion into text to store in the backend.

I mentally voted against that thinking it will be too wieldy then ended up with this supercomplex nbasty sql and ADO/DAO thing. LOL.

So, what do you think of my original solution of a temp storage for each Fieldname1 to N with the right data type and then do a the conversion to string to put in one big table?

BUT, the problem with converting everything to string in the big backend table is that then user can't do math operations on the historical data and do derived fields (F1/F2 + F3 etc.) If I do then I have to an on the fly conversion from string back to true data types? If that's the case, then I'm not sure which one is the uglier solution...

flyinghippo99

 

[boblarson]

The BEST way is to let numbers only be entered and NOT allow N/A or stuff for certain things. My question is what is the data and why can't it be entered as it should be? Where is it coming from? The way you have it now is not going to make getting the data easy anyway. And storing it all as text isn't going to be a good answer either.

If meaningful data is to be reported on, then it has to come in as meaningful data. So why can't that be entered that way?

 

[flyinghippo99]

The BEST way is to let numbers only be entered and NOT allow N/A or stuff for certain things. My question is what is the data and why can't it be entered as it should be? Where is it coming from? The way you have it now is not going to make getting the data easy anyway. And storing it all as text isn't going to be a good answer either.

If meaningful data is to be reported on, then it has to come in as meaningful data. So why can't that be entered that way?

This is the business reqs. They want to be able to view a biz data series(let's called
it F1 for fieldname1) that looks like this:

1/1/2008 534530
2/1/2008 na
3/1/2008 na
4/1/2008 4353409
5/1/2008 nr
6/1/2008 34590
...

you get the idea. That's why it's so complex...

 

[flyinghippo99]

Bob - Would it help if I use multi-value fields? Also, I was thinking using class objs in Access, but not sure if it will really help in this case...?

 

[SteveH2508]

What do NA; NR etc. actually mean?

Could they be a 'replacement' for Null or Zero?

Are the figures entered always within a certain range (i.e. are they always positive for example?) If that were so you could perhaps use certain numbers (negatives maybe) to designate NA; NR etc.. and convert their display in queries; reports etc.

 

[flyinghippo99]

What do NA; NR etc. actually mean?

Could they be a 'replacement' for Null or Zero?

Are the figures entered always within a certain range (i.e. are they always positive for example?) If that were so you could perhaps use certain numbers (negatives maybe) to designate NA; NR etc.. and convert their display in queries; reports etc.

Steve - Thanks a great point. It's all positive. So, I can map na = -1 and so forth. Is there an easy way for this in SQL? Or would I have to do it through ADO/DAO and stepping through the recordset looking for -1,-2 etc and replacing them with the strings "na" etc. just for the viewing/exporting part?

thnx

flyinghippo99

 

[flyinghippo99]

Steve - Thanks a great point. It's all positive. So, I can map na = -1 and so forth. Is there an easy way for this in SQL? Or would I have to do it through ADO/DAO and stepping through the recordset looking for -1,-2 etc and replacing them with the strings "na" etc. just for the viewing/exporting part?

thnx

flyinghippo99

Bob, Steve, et al:

I was celebrating the last few days with this new data representation since I can eliminate hundreds of tables and collapsed them into 1 big table with -1 and -2.

HOWEVER, I just remember why I had to my complex methodology. It has to do with Access 2007 limitations of 255 columns per table. I mean the -1/-2 will definitely reduce some of the complexity, but not all of it. But perhaps a bit of db design thinking can get me out of this mess...

So, here's the deal. I have hundreds if not potentially thousands of business FieldNames. My original methodology is breaking each into individual tables and combining them with a supernasty sql statement. That can be potentially eliminated
with the -1 and -2 representation.

However, I'm running into a problem. I can't put them all in one big table because of the 255 column limit in Access. So, the big table is supposed to look like this.

Index: Date
Field1
Field2
...
FieldN where N is arbitrarily BIG (bigger than 255)...

I even thought about breaking this big table into a few smaller ones by CATEGORY
but still 255 column size is NOT enough! And if I just arbitrarily break the big table into smaller tables to accommodate the many fields, then doing queries will be hard since I wouldn't know a particular FieldName is in what smaller tables unless I introduce even more machinery of an intermediate table, which might not be any
better or more efficient than my current messy but fully functional design...

Am I missing something with db design here? Or is time to consider upgrading to
SQL Server? I'm seriously considering telling my boss to upgrade to SS.

thanks!

flyinghippo99

 

[flyinghippo99]

Since this is a db design forum, perhaps, the better question is what is the best/optimal table design for the problem I described above. Instead of trying to fix the symptoms, let's get to the source. I know needing more than 255 columns seem like something is wrong with the normalization process. But that's the best I could do else I'll end up with thousands of tables. I'm not sure which one is more evil.

So, here's the detail:

1) I have close to 500 business metrics(FieldName1...FieldNameN)
2) The data is collected monthly
3) That's why I want to create one big table with Dates, Field1,..., FieldN
4) Then I have a MasterAttributeTable which has the KeyNum(primary key/index) with a list of attributes. KeyNum, Attrib1, Attrib2, ... , AttribN
5) Then I have another series of tables(current design) or one BIG table(haven't decided on this design approach yet). The table name(s) is the KeyNum and it has the following FieldNames: Dates,KeyNumFieldA,KeyNumFieldB,KeyNumFieldC,KeyNumFieldD, which are different than the MasterAttributeTable

what's the best normalization approach? Or am I stuck with hundreds of tables approach?
========================================================
Also, I've been thinking if I were to use Access as FE and SqlServer2008 as backend, and a user select a bunch of items in a listbox, then how can I send it to a stored proc in SS since SS does not accept array of values? Or is there some other simpler way to do this?
========================================================

thnx

 

[flyinghippo99]

Hi All,

I figured out a significantly better data schema already. It reduced the number of tables from hundreds to just a few. And the SQL is simpler for update, insert, delete. Ironically, the view(select) is still complicated. It's OK. I can live with that.

This forum is a great resource thanks to all the users contributions.

flyinghippo99