 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / January 2006Database design question
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this one data field - but i'm not sure) :-) Background info: Part of my project involves storing data about some objects in table "tblComponent" One of the data items(fields) is an alphanumeric identifier("fldFeature"). The identifier is composed by using L for left R for right and then a letter for which condition it has to oversimplify I'll say there are 3 possible conditions, "a", "b", and "c" so a piece might have any combination "LaRa", "LaRb", "LaRc", "LbRa", ...etc There is also a rare possible variation for any condition which could be applied as a numeric qualifier, 1 ... n So it might get as complicated as "La1Ra" etc The standard choices are finite and where I'm simplifying and saying 3 it is acutally 11 choices(120 combinations for L and R). The variations are theoretically "unknown" but in reality would be very rare and very unlikely to be more than one or two variations for a given standard condition. - to allow for unknown future variations i'm just adding this option to make the system "open ended" So I have kind of two things going on...a static list of 120 combinations (which would account for the vast majority of cases) but also a possibility of an unknown number of subtle variations which would not be static... I'm trying to figure out the best way to store these alpha "codes" I could just store the final code as a text field "fldFeature" in table "tblComponent" but after reading innumerable turtorials on normalizing databases I thought I should store the actual "legal combinations" alpha codes(120 possible combinations) in a table, "tblFeature" and just store the Primary key of that table, "fldFeatureID" as Foreign Key in the field "fldFeatureID" in table "tblComponent" but I'm not sure what advantage that would have over just storing the text string I'm also not sure if it's workable since the additional variations mentioned above are also possible and wouldn't fit in the "static" list of combinations. for the static list I could just make up a list of all possible combinations and store in a table or it could be a collection property of a class object or even a udt hard coded somewhere... Question: Which option is better? If tables are the way to go, should I just make a list of all combinations (120) (11x11 matrix) or should I have one Table to just store two records "L" and "R" and another table to store 11 records , "a"-"k" and then some kind of join or union would produce the 120 variations??? but then how to get the unknown variation plugged in there which would be user input at some stage. as you can see I'm a bit confused on what the best "layout" is for the design here. thanks for any input Mark I would store the values separately - no question. Putting the pieces together is trivial. But doing the reverse can be a real pain in the arse. I think if you store them separately, you may have a wider table, but it'll be a LOT easier to do summaries very easily. If you don't, they'll be a nightmare. You could always try it both ways, and see for yourself (no teacher like experience!), but there's no way I'd put all that stuff in one field. "Smart" keys are just a total nightmare and a basic no-no. Consider the case where you need to generate some new report based on that concatenated key - then you have to split all that junk out... not a pretty scene. Hope this helps a little. > I would store the values separately - no question. Putting the pieces > together is trivial. But doing the reverse can be a real pain in the [quoted text clipped - 9 lines] > > Hope this helps a little. Hi Piet, Thanks for the response. If I may ask a clarifying question or two...<g> > I would store the values separately - meaning you would break the alphanumeric descriptor into it's component parts and store those in separate fields in "tblComponent"? So instead of one field, "fldFeature" whose value would be something like "La1Rb2" you're suggesting breaking the string into it's component parts and creating I guess four fields... fldLeftFeature, value: "a" fldLeftVariation, value: "1" (or null if no variation) fldRightFeature, value "b" fldRightVariation, value "2" (or null if no variation) is that the idea? and just to clarify what you mean when you talk about keys this field in my plan is not a key nor a part of a key but just a piece of data about a specific object > "Smart" keys are just a total nightmare and a basic no-no. > Consider the case where you need to generate some new report based on > that concatenated key - then you have to split all that junk out... not > a pretty scene. The alpha descriptor is really one string or piece of data to me ( in my thinking process) so when I run a report I'm going to be looking for every piece who matches the following criteria There is another field, fldFamily which holds a string value such as "a" so my query would be something like 'get all "a" family items - and get a list of their feature variations Select fldFeature From tblComponent Where fldFamily = "a" 'then somehow I have to sift that recordset to find the list of all distinct features 'for simplicity I'll say there are only two possible variations in this case, either "LaRa" or "LbRb" 'then somehow group all fldFeatures to get a collection of whatever features exist in the recordset 'and then somehow do a ForEach on the collection of features 'so for argument sake lets say i get that list of two features in a collection (or probably it will be a recordset) ForEach sFeature in colFeatures Select * from <original recordset above> Where fldFeature = sFeature 'then I can do a count of those items somehow... so i end up with a report like Family"a" Feature "LaRa" count = 10 Family"a" Feature "LbRb" count = 20 so what you're saying is instead of the above scenario, do something like: Select * from <original recordset above> Where fldLeftFeature = "a" And fldLeftVariation = Null And fldRightFeature = "a" And fldRightVariation = Null is that the idea? Thanks for your input Mark > Greets, > context: vb6/ado/.mdb/jet 4.0 (no access)/sql [quoted text clipped - 60 lines] > thanks for any input > Mark You might want to check out non-relational databases first to see if they offer any advantages over relational databases for your situation. If not, many people here can help you come up with a plan to create and handle the data relationships. James A. Fortune CDMAPoster@FortuneJames.com > > Greets, > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql [quoted text clipped - 4 lines] > > normalize this one data field - but i'm not sure) > > :-) > You might want to check out non-relational databases first to see if > they offer any advantages over relational databases for your situation. I don't know too much about non-relationals either...do you have any sites in mind that would give an overview? I didn't think that was a 'current' methodology anymore but I'm trying to learn all I can about databases cause I can now see how their power could assist with some information retrieval that I need to implement. > If not, many people here can help you come up with a plan to create and > handle the data relationships. This small example was just talking about one field in one table in an overall scheme. I tried to get some pointers on overall layout of tables and fields previously but I think the question was too broad and complex for questions and answers in these forums. Thats' why I'm now trying to find teeny tiny pieces of questions to ask here even though I really need help with the broader overview of the tables design but it seems to big a question for these forums. Thanks for any info you can share. Mark > > > Greets, > > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql [quoted text clipped - 13 lines] > learn all I can about databases cause I can now see how their power could > assist with some information retrieval that I need to implement. I meant post-relational. Here's a link: http://www.intersystems.com/healthcare/healthcare.html When I saw the possibility of components with a large relational map it made me think that it might hold some promise. > > If not, many people here can help you come up with a plan to create and > > handle the data relationships. [quoted text clipped - 11 lines] > Thanks for any info you can share. > Mark Maybe your problem is simple. Many people in CDMA are quite good at normalization and should be able to give lots of advice about table structure if the problem is laid out logically. James A. Fortune CDMAPoster@FortuneJames.com > > > > Greets, > > > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql > > > > > > > > beginning learner, first database, planning stages > I meant post-relational. Here's a link: > > http://www.intersystems.com/healthcare/healthcare.html Thanks for the link I'll check that out...have not heard the term before 'Post-Relational' > When I saw the possibility of components with a large relational map it > made me think that it might hold some promise. > > > > If not, many people here can help you come up with a plan to create and > > > handle the data relationships. > Maybe your problem is simple. Absolutely it is!!! I can sum it up in one word. Ignorance.(mine) :-) Many people in CDMA are quite good at > normalization and should be able to give lots of advice about table > structure if the problem is laid out logically. well that's what I was trying (and I guess not succeeding at very well) I thought if I reduced the question down to one field in one table that would be a bite sized piece appropriate to this venue. When I tried to present the 'overall' 'schema' map to get pointers on tables/relationships it seemed to be too big a question for this type of forum. That's why i've tried to back up and just ask what I thought were very small, tightly focused, oversimplified example questions. I've been reading hundreds of articles, posts, etc about database design/ normalization / relationships etc but since this is all new I'm still just beginning to get a glimmer of what my structure will really be. I also tried to hint around at being willing to pay someone for some one on one tutoring/mentoring in terms of the overall plan but got no nibbles on that either. I'm clear on what data I need to store, what questions I need to ask of it, and what reports I need to be able to generate. I think I'm somewhat clear on what tables will need to be created to accomodate that in a normalized fashion, and to some extent what fields each table will need to include(at least initially). I'm less clear on the exact structure of every possible table and the exact syntax of each query, in order to get those questions and answers processed. I'd be happy to find that i was wrong and that someone wanted to hear the actual overview and give pointers on that as well. :-) Thanks for your help Mark > well that's what I was trying (and I guess not succeeding at very well) > I thought if I reduced the question down to one field in one table that [quoted text clipped - 27 lines] > Thanks for your help > Mark Show your schema! I do not fear it. It should be obvious right away if a relational database approach is called for. I like working at the schema level and know how to write the queries you'll need. Even more fun is determining in advance if the schema is practical for what you need and for how the data is input. Hopefully, after reading all those sources about normalization you'll understand the practical aspects of adjusting a schema. The good news is that after all the planning and making some educated guesses about the future the rest is relatively easy. James A. Fortune CDMAPoster@FortuneJames.com > Show your schema! I do not fear it. <yet> :-) It should be obvious right away > if a relational database approach is called for. I like working at the > schema level and know how to write the queries you'll need. Even more [quoted text clipped - 7 lines] > James A. Fortune > CDMAPoster@FortuneJames.com Ok, here goes... The main objects I see in my overview are: 1) Job 2) Component 3) Client (the components are physical objects - parts of a building - we design and document the details describing the objects so they can be manufactured by our various clients) So I have three main Tables: tblJob (fields: PK fldJobID, fldJobNumber, fldJobName, FK fldClientID (PK from tblClient) etc) tblComponent (fields PK fldComponentID, FK fldJobID(PK from tblJob), FK fldComponentSubTypeID(PK from tblComponentSubType - <see below>), and many other data fields) tblClient (fields PK fldClientID, fldClientName, fldClientAddress, fldClientStandards, etc and many other data fields) (each client has different standards, file templates, ways of doing things etc. - this can vary how a Component is displayed or the reports which need to be generated etc) (sounds simple so far, eh?) :-) the topic of Heirarchical objects comes in here somehow because there are (at least-<for now>) 3 SubTypes of Component... each Component object would have some common data (common to all Components) and some data unique to this subtype. so that gives rise to the need for another table (or so I'm thinking...not sure how to handle this issue) (a table of tables?) tblComponentSubType (whose PK becomes a FK in tblComponent) (fields: PK fldCompSubTypeID, fldCompSubTypeName, fldCompSubTypeUniqueValueTable?) I guess(not sure) that I need 3 "subtables" to hold the unique data fields for each ComponentSubtype??? if that's correct the above mentioned fldCompTypeUniqueValueTable is a pointer to each 'subtable' other wise since a field has to be atomic I'm not sure how to store 5 values for one type, 3 for another, and 7 for another...(as an arbitrary example) if the above is correct, then tblComponent has the fields mentioned above, a PK, a FK pointing to what Job it's associated with, and a FK pointing to what ComponentSubType it is and then the remaining fields are the data fields which are common to all components. I'm primarily wanting to get one of the SubTypes up and running first, and then later deal with the other two in their turns so for now I'll talk only of ComponentSubTypeA.(CompA for short) There are three general ways data will get input 1) some data items for a given record will be entered by a user in some way (form with textbox, combobox, option button etc) 2) some data items will be filled out by code which reads some files and gathers some data and has to input it into the database in the right place. 3) some fields will be calculated values after the previous two entries are complete, then i need to loop back through the database, reading the filled out fields and calculate some derived values from those fields. as for those calculated values the question of whether to store them in the database or just do it on the fly for generating reports is an open question and the answer may vary from one data field to another one...that is another big question I have not settled on (for now in the following description I'll assume i'm storing those values in a field) assuming I have a table: tblCompA it will have(among others) the following fields fldFamily (string) 'to which family does this belong "a", "b", "c" etc up to "aa" - "az" - "zz" etc one job may have only a couple families, another job could have hundreds of families. each family happens to be a description of it's cross sectional shape which also determines other qualities like area, etc. so lets say I have a piece which is an "a" family. that piece has a left end and a right end. Each end can be one of 11 (at this point) possible shapes, square, mitered, finished etc. I call this data "feature" (that's what my original post was about, this one data field in this one table and how to track the various possibilities) so I'm assuming I want a table or tables to store the 'legal' options for 'features' thus table: tblFeature or as I think someone was suggesting tblLeftFeature, tblRightFeature one aspect I'm not clear on, is that I have a static list of possible features... but on any given job, I may only have instances of one or two or a dozen of the possible combinations of features so that's why I was originally thinking of an alphanumeric identifier like "LaRa" or "Lb2Ra1" (variations of features) so in tblCompA I would have field fldFeature whose value would be "LaRa" Then I can ask the question, for Job 05100 how many "A" family pieces do I have with feature "LaRa" something like Select * From tblCompA where fldJobId = 05100 and fldFamily = "a" and fldFeature = "LaRa" but wait it gets still more complicated... :-) (like I said its' hard to keep this short and sweet once i get into the whole enchilada) Now I have the components of a job divided by Family and Feature, each component has a Length value. lets say one component or group of components are 24", some are 36" and some are 48", after all entries are made with initial datavalues of Family, Feature, and Length I need to sort them by length and assign an alphanumeric descriptor "ComponentName" so I need to group all Components WHERE fldFamily = "a" AND fldFeature = "LaRa" get a list of all the lengths, Once I have the list of all lenght I have to sort by longest to shortest, Find the longest length that piece or group of pieces then gets a name "A1" for family "A" and length = longest. something like dim idx as integer idx = 1 SELECT MAX fldLength From tlbComponent WHERE fldFamily = "whatever" AND fldFeature = "something" etc. then fldCompName = fldFamily & idx then idx = idx + 1 and continue looping through till they are all accounted for Then the next length the name becomes "A2" etc down to the shortest length . Then for each different FeatureSet I have to do the same dividing by length, assigning of a name, and continue through all families and all featuresets for the given job. Once all those "ComponentNames" are assigned, I have to loop back through all the records and a data field can get filled out in the table tblCompA which is fldCompName for each record in the table so now each record in the table has a Jobname, and a ComponentName (as well as many other data items) but those two uniquely identify each common item or group of items on the job. Once that happens I can ask for a report like give me a list of all CompNames and their respective counts for job "xyz". (that's one of the primary reports I need to generate.) Thats' an example of one of the calculated values that I think I need to store in the database rather than just put in a report because that name is a Critical piece of information to us and goes in various places in drawings, documents, etc. Once they are assigned and released into the wild they cant be changed and if new information comes in, new names might have to be generated which may deviate from the original ideal that the pieces are named in sequence by length so in a later stage of a job we may have "out of sequence" pieces...for that reason the original ComponentNames have to become static at some point in the process.(jobs may in some cases be released in stages) There are, of course, other data fields as well, for example: for a given family(cross section), the cross section has an area value(double). so that calls for a table of families tblFamily (fields : PK fldFamilyID, fldFamilyName ("a", "b" etc), fldFamilyArea (double), fldFamilyDescription (string) which means the field in tblCompA above the field fldFamily (instead of holding the string name would I suppose be a FK holding the fldFamilyID from tblFamily primary key)(if I'm understanding the idea of normalizing right) another calculated value is to get the volume of the piece which is it's length times it's area (with possible adjustment depending on the "features" it's left and right end may or may not have) so for example I would need to do a loop like For each oRecordSet in tblCompA For each fldFamilyID in tblFamily WHERE tblCompA.fldFamilyID = tblFamily.fldFamilyID _ For each fldLength in tblLengths AND tblCompA.fldLength = fldLength And tblCompA.fldFeature = "straight-both-ends"(no adjustment for end features) THEN tblCompA.fldCubicFeet = (tblFamily.fldAreaDouble ) X (fldLength ) or something like that.... does that give you some idea of a part of what i'm trying to do??? Thanks in advance for any input or guidance you wish to offer. Sorry this got so long winded...but I really appreciate your offer to look at this overview. :-) Mark > > Show your schema! I do not fear it. > [quoted text clipped - 88 lines] > "aa" - "az" - "zz" etc >,,, At first glance, this seems much easier to do than I expected. It's certainly much easier than some databases I've been asked to design. Unless I discover a major surprise, this problem should lend itself well to the relational approach. I'll be off until Tuesday so expect some feedback by Wednesday. I may request some sample table records later. Thanks for posting this interesting problem. James A. Fortune CDMAPoster@FortuneJames.com Peace and goodwill. > > > Show your schema! I do not fear it. > > Ok, here goes... > > > > The main objects I see in my overview are: > > 1) Job > > 2) Component > > 3) Client > At first glance, this seems much easier to do than I expected. It's > certainly much easier than some databases I've been asked to design. [quoted text clipped - 7 lines] > > Peace and goodwill. Sounds great. I really appreciate your looking at this. Hope you had a good weekend. Look forward to further conversations. :-) Mark > > > Show your schema! I do not fear it. on the subject of heirarchical objects heres some thoughts I've come up with(guesses) curious if I'm even close on this kind of layout on the topic of Heirarchical objects my current guess how to handle this abbreviations/assumptions PK = PrimaryKey FK = ForeignKey all PK are Unique, either autoincremented, code incremented or code assigned guid (to be determined) given: there's a base object with 3 variations (not sure if there's a standard accepted term for "base"... "master, main, parent...whatever) In the context of classes i think it would be called a base class with subclasses deriving or inheriting from that base object In the context of database tables it may be called Parent and the sub types children? not sure about the terminology. in any case, in my example the base object is called Component there are 3 SubTypes of Component... Precast Caststone Hardware Hardware is also heirarchical as there are 3 subtypes of hardware, Embed Panel Loose my current guess as to how to deal with subtypes is as follows. Tables: 1)table for base class data (tblComponent) 2)table for subtypes (tblComponentSubType) 3)table for subtype1 data (tblComponentPrecast) 4)table for subtype2 data (tblComponentCastStone) 5)table for subtype3 data (tblComponentHardware) in the base class table: (tblComponent) Fields: PK: fldComponentID FK: fldComponentSubType (pointer to subtype table entry) (this field is PK in tblComponentSubType) (other fields include data common to all Components) in tblComponentSubType Fields: PK: fldComponentSubTypeID fldComponentSubTypeTableName(string name of table for this subtype) at this time there are only 3 records mentioned above then for each record in tblComponentSubType there is a table listing data for that subtype tblComponentSubType1 tblComponentSubType2 tblComponentSubType3 in each tblComponentSubTypeX Fields: PK: fldComponentSubTypeXID FK: fldComponentID (pointer to item in tblComponent - this field is PK in tblComponent) other fields are data for this subtype if the above is correct layout my guess as to how to get information on a given Component whose ComponentID = 1(for example) 'assign variable to Component id we're looking for Dim lCompID as Long lCompID = 1 'Find out which subtype table to look in 'get the id for which subtypeTable to look in from the record for this component 'find the base information on this component from the generic component table Set oBaseRecordSet = Select * From tblComponent Where fldComponentID = lCompID 'out of that recordset get the subtypetable id 'Set tableId = oBaseRecordSet.fldComponentSubTypeID 'using that id get the table name dim sSubTypeTableName as String sSubTypeTableName = _ 'get the name for the subtype table from the table of subtypes Select tblComponentSubType.fldComponentSubTypeName _ From tblComponentSubType _ Where tblComponentSubType.fldComponentSubTypeID = tableID 'now you have the name of what table to look in for specialized data 'find the specialized data on this component from the proper subtype table 'assuming we have a collection of tables(remember this is just pseudocode..im sure theres an sql clause to get this actual statement) Select * from Tables.Item(sSubTypeTableName) _ where Tables.Item(sSubTypeTableName).fldComponentID = lCompID 'somehow the above two queries are "added togther" to get the full list of data for the specific item whose ComponentID = lCompID am I even close??? Thanks mark > Greets, > context: vb6/ado/.mdb/jet 4.0 (no access)/sql > > beginning learner, first database, planning stages You need to be more deliberate. Step 1 was already in the wrong direction. Step 2 was started before fixing Step 1. Step 3 was started before nailing down Step 2, much less Step 1. Then you started in on Step 4. BTW, all these steps, although premature, did give me details that will help either to come up with a relational schema that works or to point to something else. I am still at Step 1 (but making rapid progress) because your description of fldFeature, as Piet pointed out, looks like the wrong approach. To give you an idea of what I'm looking at, think of La1Ra looking more like: tblFeatures fldFID PK fldFeatureGroupID Long fldJobID Long fldEndDesc Text fldConditionID Long fldVariationNumber Long 10 3 13244 L 1 1 11 3 13244 R 1 Null given: tblConditions fldConditionID fldCondition 1 a 2 b 3 c Then a query looking for fldJobID = 13244 grouped by fldFeatureGroupID where fldFeatureGroupID = 3 will produce La1Ra (subquery details eventually). Two more lines in tblFeatures would specify an additional feature. This is just a small example of where I'm going since this idea needs to be checked against your entire schema. It's too bad that you're locked in to your 'hard-coded' initial set of sorted length values. That's a nasty angle. I'll continue to keep working on this schema and I'll probably have more questions concerning it. A bonus for me is that my main employer deals with specifications for right and left-hand manufactured components. In the meantime, given the hint of where I'm headed, try to extend this idea, if possible, to having a fldFamilyID with a fldFamilyName represent the hundreds of possible families. fldShape can be one of the fields in tblFamilies along with a field used to designate R or L. One of the beauties of all this is that you don't need fldCompSubTypeUniqueValueTable. Any family table containing fldCompSubTypeID as a foreign key will have data that connects. No downward pointers are required. In fact, if fldCompSubTypeID is a foreign key in tblFamilies then all the families (including names) for all the subtypes can be contained in that single table and fldCompSubTypeID together with fldSubTypeFamilyNumber can be used to look up the family information relative to a specific subtype. I.e., each table of components of a given type join tblComponent on fldComponentSubTypeID and fldFamilyNumber with a different set of fields available, including all the common ones. In that case tblFamilies would be a static table used to look up that information and fldFamilyNumber could be included in tblComponent knowing that enough information is present to get the family information. Let me know what you think of that idea. Let's not delve into hierarchic OO database design unless we need its strengths. So what we're looking at so far is: tblConditions fldConditionID PK fldCondition Text tblFeatures fldFID PK fldFeatureGroupID Long fldJobID Long fldEndDesc Text fldConditionID Long fldVariationNumber Long ... tblJob fldJobID PK fldJobNumber Long fldJobName Text fldClientID Long ... tblClient fldClientID PK fldClientName Text fldClientAddress Text fldClientCity Text ... tblClientStandards CSID PK fldClientID FK fldStandardName Text ... tblComponent fldComponentID PK fldJobID FK fldComponentSubTypeID FK fldFamilyNumber FK fldComponentName Text fldComponentLength Double fldComponentUnits Text tblComponentSubType fldCompSubTypeID PK fldCompSubTypeName Text tblFamilies fldFamilyID PK fldCompSubTypeID FK fldFamilyNumber Long fldFamilyName Text tblCompA fldCAID PK fldComponentSubTypeID FK fldFamilyNumber FK fldShape Text fldCompASpecificField Text I realize that this is a little abstract and Access-centric, but this is by far the best place to make design changes. Note that calculated values can still be done on-the-fly when you're forced to assign A1, A2, ... Please refer to specific tables/fields in the sample schema above when discussing the merits or shortcomings of it. Also see if tblCompA, tblCompB, etc. have enough fields in common to create a single tblCompDetails containing fldCompType provided there are no plans to increase the number of component types in the future :-). I hope this rough outline helps you get started, James A. Fortune CDMAPoster@FortuneJames.com > > Greets, > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql [quoted text clipped - 10 lines] > out, looks like the wrong approach. To give you an idea of what I'm > looking at, think of La1Ra looking more like: Thank you so much for looking at this. I will study your reply and respond back in detail asap. Thanks again, Mark > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql Hi James, First, thank you for looking at this. Second, I'm not sure I communicated clearly and/or I'm not sure I'm understanding clearly the following layout. (as far as me not having step1 figured out before going to step2...that's par for the course <vbg>) I wasn't thinking of them as step wise items, but just an overview of the different kinds of information I need to deal with. At this point in my thinking I realize I'm not clear on the final shape of all the tables.... What I see is that I have a few "separate" issues on which I'm not clear (well, more than a few probably <vbg>) (and which are (in my mind) not necessarily step wise dependent on one another - but maybe they are...that's where my inexperience with databases may be hampering my understanding of your layout) ...maybe my original post was confusing in this regard :-) As I understand your reply, you're looking at tblFamily seems to be the "Main" repository..with pointers to subtype etc For some reason I'm looking at tblComponent as being the "main" repository ... with Family just being a small piece of data about Component. I see the following 4 points as being Cardinal questions in my problem domain. 1) whether to store information about every job for every year in one giant db for the entire company (which would keep getting bigger every job every year and I would think degrading performance over time...but I'm not experienced enough in databases to know if that's true) - or - to store information which is CompanyWide (list of clients, list of jobs, etc) in one "master.mdb" and then to store job specific information about each individual job in it's own "Job.mdb" considerations: - once a job is done there is only a rare occasion that I may have to go back to a past job looking for information - once a job is one or two years old it's almost inconceivable we would need access to that information other than in the sense of archival storage in the event of some liability question years down the line(and that's highly unlikely) - there is no relation ship from job1 to job2 in terms of the data stored for that job(in the sense of "component data") (other than the company wide information like list of clients etc, which does make sense to store in companywide database) - we, at the present time, have no questions we ask which compare one job to another...they are very much stand alone projects (although I do understand that could evolve as we learn more about the power of databases...assuming I keep my job that long<vbg>) 2) how to store information about a general class of object"Component" which may be one of three different subtypes considerations: - some data will be the same for every subtype and some will be different for each subtype (that's why I thought (based on my limited studies of database theory) that a heirarchical relationship existed) (but I have no idea how to *implement* that heirarchical relationship in database terms - I was just imagining it would be a "common" table and 3 "sub" tables) I'm fine with your suggestion to *not* make it heirarchical...I just don't know what the alternative is... I can easily accept the answer that I just need three different tables one for each type and forget the one "universal" table and just accept that the three tables will have some fields which repeat from one to the next...I guess that's not really duplicating data (in the sense of normalization to prevent duplcation of data) Its' just duplication of *dataFields* not duplication of *dataValues* so maybe that's ok... (actually there is probably much more information that is different from one type to another so maybe the commonComponent table is really a waste of resources) 3) How to store "family" information considerations: - FamilyName "A" has no intrinsic meaning...it is an arbitrary selection from a list of "legal" selections relative only to a given job. - once the familyName "A" is assigned for Job 12345, it will then be related to some information, like shape, volume, weightperinch etc on a job by job basis - for job# 12344, family "A" has no relationship to family "A" for job# 12345 - family "P" for subtypeOne has no relationship at all to family "P" for subtypeTwo on the same job number 12345 (assuming that family"P" is a legal familyname for both subtypes for a given (job/client)) - the family names are originated on the fly as a job progresses, I find one kind of piece, I name it "A"(or whatever the actual designation becomes...which also varies by Client...based on their various standards for naming convention of their pieces) then I find the next piece, I name it "B" etc... maybe job 12344 has only A-C but job 12345 has A-Z (so the possible list of legal family names may also have to be stored in a table somewhere with a fldClientId pointing to a table of clients then in the table of clients there may be a pointer to the table of possible family names) Like I said I'm not sure how all this wants to look in it's final form. 4) How to store "feature" information - (the original "partial" question) Part of my problem lies in the fact that I'm not experienced in databases so my thinking does not automatically arrange itself in appropriate units in all probability My thinking is arranged around the real world units (Components) with which I am dealing. As such the "base" unit is Component. The Features discussed below are a fact about a Component. However the table structure is arranged and however the queries need to be written, each Component needs one entry in some table(or Tables) which completely describes that one component. Since there are 3 subtypes of components my thinking was that I would have to pull information from at least two tables (commontable, subtypetable) to get a full view of one component. (see my post @ 12/27/05 1:47pm yesterday about the heirarchical nature of my objects) The "Features" described below only apply to one or possibly two of the (3) SubTypes of Component. One of the facts about that one component is what do it's two ends look like (granted there are two ends but there is only one component) so the combined facts about Left end condition and Right end condition devolve into (somehow) One fact about One component. >To give you an idea of what I'm > looking at, think of La1Ra looking more like: ---------------------------------------------------------------------------- - > tblFeatures Yes -------------------------------- > fldFID PK Yes, meaningless Unique Long or GUID Key -------------------------------- > fldFeatureGroupID Long not sure what fldFeatureGroupID is it looks like it would be a FK to a tblFeatureGroup PK fldFeatureGroupID I'm not clear how you're using FeatureGroup here... I think the fldFeatureCondition you have below is what I'm calling Feature Maybe FeatureGroup and FeatureCondition are one and the same thing? -------------------------------- > fldJobID Long In my original schema I was considering using a separate .mdb file for each job we do. We store all files re a given job in a "job folder" on the server. It made sense to me to store a .mdb file there as well. However, when asking about the merits of PerJob or PerCompany .mdb philosophy I was recommended to use PerCompany. That to me complicates the layout of the .mdb, making it a huge database storing all information about all jobs we do. But since that was what was recommended, that is the direction I'm trying to go. The reason I mention the Job question is that you have fldJobID in tblFeatures. I thought fldJobId would be in tblComponent. In other words, a JobId is a fact about a component, and a feature is also a fact about a component, but a JobId is not really a fact about a Feature.(at least in my mind) perhaps you can comment on that relationship in your schema below. In my understanding to get information about a job, I would filter tblComponent on fldJobID, then filter that result on whatever other criteria applied for the specific query. -------------------------------- > fldEndDesc Text this would imply I would need two records for each Component, one whose fldEndDesc = "L" and another for fldEndDesc = "R" what about if I had two fields...fldEndLeft and fldEndRight...wouldn't that allow me to have only one record per piece? (and again, I think these fields would be in tblComponent or tblComponentSubTypeOne but not in tblFeature) -------------------------------- > fldConditionID Long -------------------------------- > fldVariationNumber Long > [quoted text clipped - 7 lines] > 2 b > 3 c > Then a query looking for fldJobID = 13244 grouped by fldFeatureGroupID > where fldFeatureGroupID = 3 will produce La1Ra (subquery details > eventually). While my queries will always be filtered for just one job at a time, I think of the primary object to query is Component So I think of the query as (pseudo sql)<g> Select "allComponents" where fldJobId = "currentJob" Now I have all components for Job 13244 Now from those components I have to sort them by "FeatureSet" Select "allComponents" from "PreviousSet" Where fldFeatureLeft = "A" And fldFeatureRight = "A" Now I have all components whose leftEnd = a and whose Right end = a Now I can sort Those Components by Length... Two more lines in tblFeatures would specify an additional > feature. This is just a small example of where I'm going since this > idea needs to be checked against your entire schema. I'm not understanding some things here...:-( hopefully (if I haven't worn out my welcome already <g>) some further conversation will clarify It's too bad that > you're locked in to your 'hard-coded' initial set of sorted length > values. That's a nasty angle. I'll continue to keep working on this > schema and I'll probably have more questions concerning it. A bonus > for me is that my main employer deals with specifications for right and > left-hand manufactured components. I think somewhere in the following is where I'm not understanding exactly how you're dividing up the information I'm sure you're correct in your assesment...I'm just not "getting" it yet... I'll keep working on it and maybe if you respond to my responses you can help me understand why this scenario is better than how I'm thinking of having different tables for each subtype In the meantime, given the hint of > where I'm headed, try to extend this idea, if possible, to having a > fldFamilyID with a fldFamilyName represent the hundreds of possible [quoted text clipped - 16 lines] > database design unless we need its strengths. So what we're looking at > so far is: ---------------------------------------------------------------------------- - > tblConditions not sure where you're going with this one...is this the variations per given feature? like La1 versus La ? > fldConditionID PK > fldCondition Text ---------------------------------------------------------------------------- - > tblFeatures I'm still not clear on this one...see comments above > fldFID PK Yes, meaningless Unique Long or GUID Key > fldFeatureGroupID Long > fldJobID Long > fldEndDesc Text > fldConditionID Long > fldVariationNumber Long > ... ---------------------------------------------------------------------------- - > tblJob Yes > fldJobID PK Yes > fldJobNumber Long Yes > fldJobName Text Yes > fldClientID Long Yes > ... ---------------------------------------------------------------------------- - > tblClient Yes > fldClientID PK Yes > fldClientName Text Yes > fldClientAddress Text Yes > fldClientCity Text Yes ---------------------------------------------------------------------------- - > tblClientStandards Yes > CSID PK Yes > fldClientID FK Yes > fldStandardName Text Yes ---------------------------------------------------------------------------- - > tblComponent Yes, I see the need for a table of components (this holds all data which is common to all component sub-types (in my thinking)...if that's correct?) This is in my thinking the "Main"/"Master" (whatever) table in the whole database....all other tables exist to clarify the data about this component -------------------------------- > fldComponentID PK Yes -------------------------------- > fldJobID FK Yes -------------------------------- > fldComponentSubTypeID FK Yes -------------------------------- > fldFamilyNumber FK not sure but wouldn't this be fldFamilyID from tblFamilies below? -------------------------------- > fldComponentName Text yes, this is the final name derived/calculated/stored data after all components are entered/sorted/and named -------------------------------- > fldComponentLength Double Yes -------------------------------- > fldComponentUnits Text Not sure what this one is...is it a description of what units the length value above is measured in??? eg("feet" or "inches" or "meters" etc)??? if so that's a good idea which I had not considered...we usually would measure it in inches but that 's good to make it a mutable field for future growth/adaptation ---------------------------------------------------------------------------- - > tblComponentSubType Yes, I see the need for a table separating the 3 subtypes of components -------------------------------- > fldCompSubTypeID PK Yes -------------------------------- > fldCompSubTypeName Text Yes (this table would - at current point - have 3 entries "Precast" "CastStone" "Hardware") does this relate to my other post @ 12/27/05 1:47pm yesterday about the heirarchical nature of my objects? ie base object = Component sub object = ComponentSubType (one of the subobjects is Hardware...which also has subtypes (HardwareStruct, HardwarePanel, HardwareLoose) ---------------------------------------------------------------------------- - > tblFamilies ----yes I see the need for tblFamilies (but it may vary per client and per job) this table holds the "legal" family names this has to relate back to tblClient somehow as each client may have different list of Legal family names for example clientOne disallows certain letters...ie don't use DIOQSUV clientTwo only disallows DIOQ etc so it's almost like I need a tblFamily for each client in tblClients or a pointer to a list of disallowed letters...or something In the user operating the program he should be able to enter JobNumber (that will automatically setup which client we're dealing with) then he enters (lets' say there's a button on a form to run the sub "GetNextFamily" and the next un-used family name pops up out of the legal list for that client(job) or perhaps a combo box is shown with a list of all legal names for that job and he selects from that.... so there's kind of like two groups of family names for a given job - the legal list of all possible choices(at start of job)...and the resulting list of all used choices on this job.(at end of job) so maybe I need two tables - tblFamilyLegalList and tblFamilyUsedList...??? just guessing here and again there may be multiple tblFamilyLegalLists (one for each client)(or some clients may share the same list so it's not necessarily one to one relationship...maybe there are 3 lists and 10 clients and each client gets a pointer to one of the three lists...something like that -------------------------------- > fldFamilyID PK yes, I see the need for a PK ID field -------------------------------- > fldCompSubTypeID FK Here, I'm not sure why fldCompSubTypeID is in tblFamily....perhaps you can illuminate your thinking in this item I think fldCompSubTypeID would be in tblComponent... in other words "what subtype of component is this component?" is a fact about a component - not a fact about a familyname although, all components will have a familyname though how they are named may vary by what subtype they are and also on a given job I may have a ComponentSubTypeOne(Caststone item) with a family name of "P" and a ComponentID of "1" so the components "fullname" would be = "P1" and on the same job I may also have a ComponentSubTypeThree(hardware item) and for that client, his standard for hardware cast into a panel might be "P" so one item of hardware for that job may also be named "P1" thus the family names for each componentSubType need to be segregated somehow.... which I understand would be accommodated by your fldCompSubTypeID so maybe that's the secret ingredient...I need to gestate on this one for a while I think....I just don't understand yet why store it in tblFamily rather than in tblComponent is simplicity morphing into complexity yet? :-) ------------------------------- > fldFamilyNumber Long I don't see the need of a fldFamilyNumber, but maybe you see a need for this I'm missing, could you elaborate? I think this may be a duplicate of the PK field above??? (fldFamilyID) -------------------------------- > fldFamilyName Text Yes, this is my primary data(fact about component) this is one record in the list of legal names right? ---------------------------------------------------------------------------- - > tblCompA Yes, if this is one of the Heirarchical subtype tables? but you said forget heirarchy for now so maybe it's not??? If it is, i would have tblCompA, tblCompB, and tblCompC for the three different subtypes??? is that right? -------------------------------- > fldCAID PK Yes -------------------------------- > fldComponentSubTypeID FK This seems like the samething as fldCAID above??? -------------------------------- > fldFamilyNumber FK I would think this would be in the 'main' component table (tblComponent) since all components would have this data but I'm not sure if you're using my idea for how to divide "heirarchical" entities as in my other post -------------------------------- > fldShape Text derived from fldFamilyNumber above...I would think this would be in tblFamilyName...ie what shape does family"A" refer to on Job 12345 -------------------------------- > fldCompASpecificField Text Yes, if this is the "heirarchical" table for this sub type then I would have multiple fields like this describing the facts about this component fo this sub type I think in this subtype table (and all other subtype tables) I also need a pointer to which piece(component) is being described so I would add a field here fldCompID FK (PK from tblComponent.fldCompID) does that sound right? > I realize that this is a little abstract and Access-centric, but this > is by far the best place to make design changes. Note that calculated > values can still be done on-the-fly when you're forced to assign A1, > A2, ... I realize they *can* be calculated on the fly, but once done they become immutable...if later entries are added after a part of a job has been released, the calculations for already released pieces cant' change...only new items can be added and if they are 'out of sequence' then they have to be so identified in some fashion...that's why I thought I would store this data after the initial calculation was done. so on any further re-calculation(if pieces were added) I would check this field for "NULL" and if it was Not Null then I leave it alone... does that make sense??? Please refer to specific tables/fields in the sample schema > above when discussing the merits or shortcomings of it. Also see if > tblCompA, tblCompB, etc. have enough fields in common to create a > single tblCompDetails containing fldCompType provided there are no > plans to increase the number of component types in the future :-). > > I hope this rough outline helps you get started, again, I can't thank you enough for helping me to think this through.. I relize this is a big complicated affair well beyond the usual short question answer on this forum...thats why my initial hesitation let me know If I'm overstepping my bounds on this forum I really appreciate your willingness to help. I also understand this is more than a 'small' question and if you wish to deal with this in a way other than on ng let me know. Thanks Mark Propst (mark atsymbol atreng dotsymbol com) (I'm not as brave as you are if that's your real email <vbg> though even without printing email address those spammers have no problem finding me anyway so don't know how much good my paranoia does <g>) > James A. Fortune > CDMAPoster@FortuneJames.com > > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql > [quoted text clipped - 45 lines] > > James A. Fortune > > CDMAPoster@FortuneJames.com Mark, It looks like I'm going to have to go into much more detail about this than I imagined. Let me make a few short comments and then I'll need some time to give you a more detailed explanation in another post. If you don't mind I'll start back at Step 1 :-). I'll start with the decision to use two records to store each component. That's a good observation and a good question. You'll note that having a separate table called tblConditions allows you to have as many conditions as you want without having to deal with a fixed list of combinations (or unfixed list if any new conditions are added). Also note that having tblFeatures allows for as many Features as you want to be attached to the job, namely, a Group of features. Since the tblFeatures.fldFID is a primary key (probably AutoNumber), fldFeatureGroupID allows you to distinguish all the various Features assigned to a given job. Although your observation is correct that adding more fields would allow you to add a feature using a single record (think molecule), I decided that each end constituted a more fundamental decision (think atom). What questions do you need to ask yourself in deciding whether to use one record or two records to store a feature? The first question would be something like, "Is the RxmLyn way of assigning features ever going to change?" The second, more pragmatic question, would be something like, "Will I ever need to query summary information about just one side or the other?" and, if so, "Does a particular table layout make doing something like Totals any easier?" If you feel quite confident that those questions have been considered you can decide whether to use a single record or to use two records to store your Features. Note that the single record schema also allows you to do most summary queries that you're likely to encounter. I have found in the past that having more records rather than more fields gives me more flexibility to deal with unexpected future requirements. One record per Feature is fine once you've considered the ramifications. Note that with the single record schema another category in addition to R and L, if that's even possible, requires adding more fields to tblFeatures and could complicate/limit totals queries even more. My last few comments are more general. I would not give each job its own mdb. Access can handle putting all the jobs in one mdb. At first I looked at tblFamily as being a main repository and changed that to tblComponent and other tables as being the main repository. I see tblFamily as mostly fixed. Give me another day or so to come up with a more detailed explanation about why I started where I did. James A. Fortune CDMAPoster@FortuneJames.com >> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql >> [quoted text clipped - 15 lines] > fixed list of combinations (or unfixed list if any new conditions are > added). I think we're getting real close to something I can understand. I need to make sure I'm understanding your terminology though. I think your tblConditions is what I originally thought of as tblFeature...ie a table of possible end "features"...actually I like your term "Condition" even better than my term "feature" But then you also talk about 'feature' as well so I'm not sure how you're dividing these two terms. To make sure I'm following you I will try to describe again the data I need to store so we can use the same terminology and I can be more sure i'm understanding your layout. Initially I was trying to evolve my plan from general to specific. My basic unit is a component. There are three different kinds of component. So I was thinking in an object oriented way that they were subtypes of an "ideal" "concept" of component But maybe that is not the way to go with this database. maybe i should let go of the concept of subtypes with a universal parent and just say I have three different kinds of objects I need to track in completely separate ways. so for now I'll ignore the possibility that there are two other subtypes I need to deal with and just talk about ComponentOne (Comp1) for now. The subject of "end condition" or "end feature" only applies to Comp1 I have a piece of stone (Comp1). The piece of stone has a shape. I need to identify all possible variations using a common system so I can divide up stones into "matching" shapes. Most(but not all) instances of Comp1 are linear in nature, that is, they are longer in one axis than in the other two, like an extruded bar of aluminum for example. That's why I started describing them by saying they have a "crosssection" (Family) and a Length. Now if they were all squared off at the ends they would only need two pieces of information to identify common pieces, Family and length. Unfortunately, they aren't all squared off at the end, some are mitered, some have finished ends, some have return ends etc Thats what I originally called "Feature" or "End Feature" or "FeatureCode" This data comes from a fixed list of possibilities (11 basic options at this point - maybe more in the future) (Maybe that calls for two tables...one to hold the list of all possibilities, and one for the possibilities which actually occurred on this job...not sure about that yet) Now that you bring in the term "Condition" I prefer that term to "Feature" So from here on out I will use the term "EndCondition" And due to it's (usually) linear nature it has two "EndConditions" one Left and one Right. so now to describe the piece I have Family,LeftEndCondition,RightEndCondition, and Length. In rare cases the end condition could have a variation. Lets say 90% of the pieces have "Straight" end conditions. But two of those pieces have a small notch cut out of one end. So I have to allow for an unknown infinitely variable possibility of variations for any given "EndCondition" So I was just using a numeric modifier to indicate variation (if it occurred) Once a variation is assigned it has to be stored for that job so if another piece is found with the exact same variation, the original identifier will be used but if another variation is found, the next identifier will be added to the table of EndConditionVariations Since the variation is unknown and infinitely variable I figure I'll allow the user to enter some short text description like "notch 2 X 2 X 2 top front corner" that description will then be linked with a numeric identifier and can be seen in a combo box or list somewhere for the user to see as they identify other pieces they come upon. Since the variation is unknown and infinitely variable it is not coming from a fixed list of possibilities like the EndConditions are...it has to be user entered in the simplest possible way, and then stored so they can see what variations they have already created in case they come up with another instance of the same variation they dont' recreate it with a new name, they select the one they already have which again applies to their current piece - or if it's a new variation they create a new entry in that table of variations So now I have Family, LeftEndCondition, LeftEndVariation(maybe), RightEndCondition, RightEndVariation(maybe), and length. Now I think I have a full description of the piece which would allow for any possibility. Maybe that is what you are calling the "FeatureGroup"??? (which is what I originally started out calling a FeatureCode since I was using a single concatenated string to identify those 4 pieces of data - not counting family and length - ) The combination of those (possibly) 6 pieces of data...(4 if there are no variations...or the variations can be a Null value and just leave it at 6 pieces of data) appear to be sufficient to describe one piece that above description works for the vast majority of instances of Comp1 Except what about the pieces which are not linear in morphology, for example a round column, or a square post cap....not sure how to generalize the system to allow for all possible shapes. Maybe I need another layer of discrimination...like linear group, column group, rectangular group I can see this is tricky trying to come up with a generalized system to describe any possible shape of object. Maybe i need an exception class...???? In any case, leaving aside the question of the exceptions and just dealing with the linear group, I say I have 6 pieces of data to represent the morphology of one instance of Comp1. I cant' seem to get away from my concept of a database table... I think of the table as representing a "kind of a thing"(entity) I think of a Record as being an "Instance of a thing" and I think of a Field as being a "Fact about this instance of this thing" That's why I cant' get it out of my head that I need to store left and right end information in the record that describes the "thing" ("Component") I'm not trying to be stubborn I'm just not understanding how to get away from storing the information about the Components left and right end conditions in the record about the component. But please don't give up on me,,,I want to learn...if you can explain a better way I'm all ears So how about this idea? tblJob fldJobID PK fldClientID FK (from tblClient.fldClientID PK) ...all other fields tblClient fldClientID PK ...all other fields tblComponent fldCompId PK fldJobID FK (entry from tblJob.fldJobID) fldFamilyNameUsedID FK (entry from tblFamilyNameUsed PK) (can not be NULL)(see table below) fldLeftEndConditionID FK (entry from tblLeftEndCondition PK) (can not be NULL) fldRightEndConditionID FK (entry from tblRightEndCondition PK) (can not be NULL) fldLeftEndVariationID FK (entry from tblLeftEndVariation PK) (can be NULL) fldRightEndVariationID FK (entry from tblRightEndVariation PK) (can be NULL) fldCompLength (I don't think I need a table of lengths cause they're infinitely variable...just have to be > some minimum legal value which can be checked in some kind of data validation routine) ...all other fields ...not sure about this idea...the next two tables are repetitive...duplicates...one for left and one for right ...the reason I did this was so the Component record could have a FK whose name matches the PK in each of the next two tables (I think the FK and it's associated PK have to be named identically but I'm not sure....everything I've read shows it like that but I've not seen it explicitly spelled out that that was a requirement) (philosophically speaking<g> it seems wasteful and redundant...but since there are only a dozen approx items in each table as far as resources go it's nothing compared to the hundreds of thousands of entries in the tblComponent over all jobs over all years of operation so maybe it's not so bad) tblLeftEndCondition fldLeftEndConditionID PK fldLeftEndConditionDescription (these dont' need to be this long in reality but for clarity I'll try to not abbreviate here) fldLeftEndConditionAbbreviation ...that table has 11 entries at this point...more can be added if need arises in future (i don't think I need a FK fldJobId here cause these are just a standard list of choices available for any job) tblRightEndCondition fldRightEndConditionID PK fldRightEndConditionDescription (these dont' need to be this long in reality but for clarity I'll try to not abbreviate here) fldRightEndConditionAbbreviation ...same 11 entries as left table above ...again, two identical tables...one for left and one for right??? ...again, just so the FK of parent is name same as PK of child(or is that visa versa?) (this table may or may not have any entries for a given job) tblLeftEndVariation fldLeftEndVariationID PK fldLeftEndVariationDescription (entered by user, displayed in list somewhere or combo box etc) fldJobID FK (from tblJob) (here I put a reference to what job since the variations have to be identified on a per job basis) (this table may or may not have any entries for a given job) tblLeftRightVariation fldRightEndVariationID PK fldRightEndVariationDescription (entered by user, displayed in list somewhere or combo box etc) fldJobID FK (from tblJob) Although your observation is correct that > adding more fields would allow you to add a feature using a single > record (think molecule), I decided that each end constituted a more > fundamental decision (think atom). What questions do you need to ask > yourself in deciding whether to use one record or two records to store > a feature? The first question would be something like, "Is the RxmLyn > way of assigning features ever going to change?" possible if I completetly redesigned my thinking and program but it works for me so at this point I dont see a need to revise it. Regardless of how the information is stored in the database it's useful to me in other parts of the program to have a 10 character(plus or minus) "FeatureCode" to enter in certain places in other files/drawings/drawing objects in order to "encapsulate" the full description of the "character" of the piece for the user. so even if I store the individual pieces of data in different tables or different fields for the sake of database operations...I'll probably be getting that data out of the database and recombining it into a short 'FeatureCode' for use elsewhere.... or possibly be extracting the "featureCode' from already existing files/drawing objects via code, then breaking the string up into it's component parts and storing them in their respective places in the database(wherever we end up deciding to store them) (I originally used LaRa as an abbreviated way to describe a simple feature code...in actual practice the code is made up of the abbreviations for the various end conditions we've identified. eg "Straight" = "ST" so the simplest piece(the vast majority) will be "LSTRST" and is human readable by the user instantly to see that it's a piece with a left straight end and a right straight end. likewise if "Return End" = "RE" then LSTRRE = left straight right return ... since we only have a few variations and in happy coincidence their first two letters are all unique among the 11 variations, its' an easy string operation in vb to grab the abbreviations from the descriptions and visa versa and it's easy for the human user to also interpret it at a glance with a minimum of training. :-) all I'm doing with it is saying this piece has (as another example) a straight left end and a mitered right end (LSTRMI) so that defines a certain morphology of an object and the concatenated string was just a convenient (in my mind) way to encapsulate that data into a small chunk of data I could pass around the program for the purpose of grouping objects according to their shape. That idea came to me before realizing that a database would be another improvement in the ability of my program to track data...so now this is a whole new world of how to group 'facts' and I'm trying to catch up to you in how you're thinking of storing these different 'facts' (I am currently storing and sorting the familyname, featureCode, and Length in Scripting.Dictionaries and collections...the idea of moving to database makes it much more powerful and flexible...that's just a little history on how i got to this point in the first place) The second, more > pragmatic question, would be something like, "Will I ever need to > query summary information about just one side or the other?" I can't at this point imagine any possible reason to do this...all I need to track is the piece(Component) itself. The two ends are inextricably attached to the Component and have no meaning separated from said Component. (I never need to know how many Left miters - irrespective of right hand conditions - I have) (I only need to know about whole Components)...at least at this point I cant' imagine what I would do with that finer granularity of data...that doesn't mean the need cant' arise in the future...so I appreciate the ideas that database storage is opening up to me in how one can separate various nuances of a piece of data. and, if > so, "Does a particular table layout make doing something like Totals > any easier?" If you feel quite confident that those questions have > been considered you can decide whether to use a single record or to > use two records to store your Features. Note that the single record > schema also allows you to do most summary queries that you're likely > to encounter. I have found in the past that having more records > rather than more fields gives me more flexibility to deal with > unexpected future requirements. That is a good piece of advice I'm taking to heart One record per Feature is fine once > you've considered the ramifications. Note that with the single record > schema another category in addition to R and L, if that's even > possible, requires adding more fields to tblFeatures and could > complicate/limit totals queries even more. well, maybe I'll think of some reason to identify "middle" condition of piece...maybe it has a lump in the center of it...so that's a good thought, even though at this point 99% of all pieces will only be watching their Right and left ends I do like the open ended flexibility of using Records instead of Fields to allow for future expansion/modification. > My last few comments are more general. I would not give each job its > own mdb. Access can handle putting all the jobs in one mdb. That's what everyone else says also so I'm accepting the concept. Since I'm new to database it seems odd...I would think that after hundreds of pieces per job over hundreds of jobs per year over 10 or 20 years that database is going to get pretty big... I would also think, but maybe it's not true, that the bigger the database got, the slower things would run, but maybe that's just my proceedural thinking at work. ;-) At first > I looked at tblFamily as being a main repository and changed that to > tblComponent and other tables as being the main repository. That jives more with how I see it also. I see > tblFamily as mostly fixed. Ok I see two tables for Family. One holds the legal names allowed on a per client(job) basis. Actually it either holds the legal letters or the disallowed letters depending on how I want to code the selection process...either way it ends up the same functionality The other table holds the "Used up" letters (or combination of letters) which are currnently in use on a per job basis. so ...this is a static table...all legal names for all clients grouped into one table(is this what's called a Look up table???) tblFamilyNameAllowed fldFamilyNameAllowedID PK fldFamilyNameAllowedText fldClientID FK (from tblClient PK - each record identifies the client for whom this is allowed (or not allowed) tblFamilyNameUsed fldFamilyNameUsedID PK fldFamilyNameUsedText fldJobId FK (from tblJobList PK...all used families can be found on a per job basis this way I think) ...I suppose I could add a field here to hold each componentId that uses this family name but I'm not sure I need that or how I would use it...just a thought...you may see a way to use it with your knowledge of sql... example: fldComponentIDThatUsesThisFamilyName FK (from tblComponent)???? ...that's kind of like a cross referencing index...the tblComponent has a pointer to this table and this table has a pointer back to the tblComponent...don't know enough about db to know if thats useful or stupid!<g> or something like that so when I start a job and need a family name ... in my most fluent sql pseudo code <vbg> I say, I'm working on Job number "ThisJobID" (which identifies which client it is since client id is fk in tblJobList) so look in tblFamilyNameAllowed where fldClientID = tblJobList.fldClientID And tblJobList.tblJobID = "ThisJobID" and if that entry does not appear in tblFamilyNameUsed Where tblFamilyNameUsed.fldJobID = "ThisJobID", then use this entry, Else ..."getNextEntryForThisJobId" does that sound right??? Give me another day or so to come up with > a more detailed explanation about why I started where I did. > > James A. Fortune > CDMAPoster@FortuneJames.com Again, I can't thank you enough for helping me to think through all this. As you can imagine, if it's making you scratch your head just a little...imagine my confusion when I'm completely new to databases! I don't feel quite so bad now that I didn't come up with the final solution in five minutes on my own, now that I'm getting confirmation that there is at least a tiny bit of complexity here to figuring it all out. Looking forward to your further ideas. Kind regards, Mark > >> > > context: vb6/ado/.mdb/jet 4.0 (no access)/sql > >> [quoted text clipped - 393 lines] > Kind regards, > Mark I see that the initial schema provided a basis for discussion but not exactly in the way I intended. I'll try to digest the information you just provided and generate a new sample schema that is more limited. That will provide a starting point for adding the rest of the schema in future posts. Getting the best schema you can up front is crucial for avoiding problems later. Experience just helps you know which ones are likely to cause problems. In your case, designing a schema is not complex at all -- unless you want a good one. Actually it's mostly experience and thinking ahead mixed with some planning. You may have a tiger by the tail. You're certainly going to need a decent plan to pull it off. This problem has you looking like a puppy trying to get its teeth around a large cowbone. BTW, when the A1, A2, ... are assigned, Access can put those names in a different field. James A. Fortune CDMAPoster@FortuneJames.com > I see that the initial schema provided a basis for discussion but not > exactly in the way I intended. Am I just being dense and not getting something? > I'll try to digest the information you > just provided and generate a new sample schema that is more limited. [quoted text clipped - 7 lines] > pull it off. This problem has you looking like a puppy trying to get > its teeth around a large cowbone. > BTW, when the A1, A2, ... are > assigned, Access can put those names in a different field. Right. (That's one of the (several) ..."other fields as needed"...referred to previously) NB I've been trying to make it clear that I'm not using Access. Since I was also (cross)posting to an access group as well as the ado groups I tried to be clear about that. "context: vb6/ado/.mdb/jet 4.0 (no access)/sql" Ps I just went back and reviewed my original post and see that I used the term Condition and Feature interchangably...I didnt' realize that when I later thought You were introducing a new term. Sorry for the confusion. (I guess at the time i unconsciously realized that Condition was a better description than Feature) :-) Thanks again for all your help. I hope Lyle isn't going to get too mad at you for helping me. I'm not mad at all, just astonished at Jame's patience and perseverance. > I'm not mad at all, just astonished at Jame's patience and > perseverance. Yes, I too admire his patience, perseverance and generosity. Happy New Year :-) <snip> > NB I've been trying to make it clear that I'm not using Access. > Since I was also (cross)posting to an access group as well as the ado groups > I tried to be clear about that. > "context: vb6/ado/.mdb/jet 4.0 (no access)/sql" <snip> > Thanks again for all your help. > I hope Lyle isn't going to get too mad at you for helping me. Are you getting any input from any of the other groups you have posted to? No? That's because you haven't explained what a 'component' is or even what the purpose of storing one is. You have only tried to explain your own attempt at designing tables. You must be handling this information in some way now. How? When you view a 'report' on a component, what does it look like now? Maybe post a picture of a few for download somewhere (not as an attachment here) if you can. The only way I know of 'normalizing' data is to start from a required/desired view of that data. > <snip> > [quoted text clipped - 15 lines] > That's because you haven't explained what a 'component' is or even > what the purpose of storing one is. my apologies if I wasn't clear on that. In my first post I was striving for brevity. after getting the ok from James to post an overview I went into more detail (apparently too much detail for Lyle <g>) for example my post of 12/22/05 at 9:02 above has what I thought was a fairly clear, if abbreviated, overview of my general problem domain (even though I'm still leaving out a lot of details that I'll have to figure out eventually...I thought I should pare things down to just the basics to keep it as brief as possible) > You have only tried to explain > your own attempt at designing tables. Well, again, sorry if I posted inappropriately, in most of the other code ngs people like to see that the op is at least trying to do something on their own and then asking for help like, this is what I'm trying..what am I doing wrong or how can this be done better... that's what my intention was in showing how I imagined (in my admitted inexperience) a table design might look like. Not sure how I should be approaching this if everything I've done so far has been wrong! :-) > You must be handling this information in some way now. > How? as I mentioned in my post of 12/30/05 at 1:21am "(I am currently storing and sorting the familyname, featureCode, and Length in Scripting.Dictionaries and collections...the idea of moving to database makes it much more powerful and flexible..." > When you view a 'report' on a component, what does it look like > now? Maybe post a picture of a few for download somewhere (not as an > attachment here) if you can. I havent' yet designed the reports...at this point our company doesnt' use databases at all. We're a small company, (no one here is a professional programmer). I'm trying to learn ways to improve how they do things now. We deal with (cad) drawings. Our end product is a dwg of a building with each piece labled with a name or piecemark. A1, A2, B1, B2 etc. We also have to derive a list of how many A1's A2's etc. Right now it's all done by "hand", someone looks at the dwg, counts the pieces and assembles a count "manually". I'm trying (on my own time, and not being a professional programmer) to develop a system that will be of use to the company in automating to some extent and eliminating some human error. > The only way I know of 'normalizing' data is to start from a > required/desired view of that data. Though it's probably not what you would consider a "Report" as such, the information I want to extract has two levels of detail...one for the job as a whole and the other for each individual piece on the job. Individual: Piecemark: A1 SectionFileName: "A-Section.dwg" Length: 12" Count: 24 CuFt: 1 Weight: 150 Job Report: Job Number: 12345 Piece Count List: A1 - 24 A2 - 36 B1 - 123 B2 - 3 etc Although "reports" are apparently the end product of most databases, in our case I was looking at the possibility of entering items into our drawings (in various ways using code and the api of our drawing program) by reading data stored in the database in various tables and putting that data into different parts of different drawings where it's required. So I don't have a large list of "Reports" as such which I need to output. I can see that that will be an added advantage in the future if I can figure out how to store the data in the first place. Once I started researching databases I began to see all kinds of possibilities that could improve how we do things. At this point it's just ideas in my head that I'm trying(with the generous help of people here) to find ways to implement. I'm sorry if I've offended anyone here, or misused this public forum. Since I'm just beginning I don't know how to ask the right questions in the briefest manner while including all salient information. I'm certainly open to constructive criticism...even destructive criticism...what the heck <g>... as long as no one starts hitting <vbg> Thanks to all for their help and advice Mark > I'm sorry if I've offended anyone here, or misused this public forum. > Since I'm just beginning I don't know how to ask the right questions in the > briefest manner while including all salient information. I don't think you have done anything to offend anyone and the last thing you want to do is get sensitive about any comments you might receive. I've been following your thread from the beginning and still do not have a clear picture of what you are hoping to accomplish. No harm done. I may just be dense. I see now that you are just thinking out loud. The Denseness sickness is spreading all around the Lake. I have it too. > > I'm sorry if I've offended anyone here, or misused this public forum. > > Since I'm just beginning I don't know how to ask the right questions in the > > briefest manner while including all salient information. > > I don't think you have done anything to offend anyone and the last thing > you want to do is get sensitive about any comments you might receive. Ok, roger that! :-) I guess I was a little nervous from the beginning due to 3 observations I considered. 1 I couldn't describe my entire question in a one liner. 2 ng's are primarily geared toward a short snippet level of detail. 3 this being an access group and me using only ado/sql/vb6 and no access so I didn't mean to get sensitive but i want to be respectful of the others around here and I'm kinda standing out like a sore thumb cause I'm a rank beginner and my posts are like, what?, 10 times the average size in kb <vbg> So actually I thought Lyle's comment about the Leather bounding was pretty cute and it made me laugh but I was worried he was really upset with me using too much bandwidth on the group here. > I've been following your thread from the beginning and still do not have > a clear picture of what you are hoping to accomplish. No harm done. I > may just be dense. I see now that you are just thinking out loud. apparently my loud thinking is not being very clear in describing the data I want to store :-) like I said, its' hard for me as a beginner to know how to phrase my question about the best way to store this information...I guess my inner confusion is just filtering out of my fingers onto the keys and ending up piled over here. :-) Thanks for chilling me out. Mark > > Although your observation is correct that > > > adding more fields would allow you to add a feature using a single > > > record (think molecule), I decided that each end constituted a more > > > fundamental decision (think atom). Hi James, You may be tired of hearing from me by now...but in the event you *haven't* killfiled me ...<g> I think I'm finally understanding your idea of keeping the data about "Condition/Feature-and-or-Variations" in a separate table. Since that table can have a pointer to the componentID, I don't think I need any entry at all in the tblComponent that refers to Condition/Feature! I think I'm finally realizing that the sql query will combine the components data together so they don't actually have to be stored in the same table. (completely the opposite of the concept of OO where a class encapsulates it's data...I was thinking of table=class and field=property...so thats why I kept thinking the tblComponent had to have a field(or fields)describing it's conditions...) sorry for being so slow on the uptake. using your previously supplied schema as a template I have the following modification to offer for critique 'Table to hold list of possible end conditons ... tblConditionList fldConditionID PK fldCondition Text eg: Condition(0) = "STRAIGHT" Condition(1) = "RETURN" Condition(2) = "FINISH" etc Condition(10) = "SPECIAL" ' since entries in Special category will need to be user defined, stored, described and shown to user in combobox or other control, it will require another table to hold it's entries ... tblConditionSpecial fldConditionSpecialID PK fldConditionSpecial Text (user defined description of special condition) fldComponentID FK <from tblComponent>(which component is this associated with) ' also since any of the conditions can have a user defined variation we also need a table, ... tblConditionVariation fldConditionVariationID PK fldConditionVariation Text (user defined description of variation) fldConditionID FK <from tblCondition> (which condition is this a variation of) fldComponentID FK <from tblComponent> (which component is this associated with) 'the following table is what conditions were encountered... 'so each component gets multiple entries in this table....as many as required to describe piece. 'that allows for future expansion if you need to add more records per piece...like one for middle.... 'the entries all get linked with the ComponentId FK to determine which group of features goes with which Component. 'This eliminates need for a field in tblComponent to point to condition for that component...(i think) ... tblFeatures (may rename this later...tblConditionActual??? for consistency of naming convention) fldFeatureID PK fldComponentID FK <from tblComponent>...(which component is this record applied to) fldEndDesc Text (Left, right, middle, userdefined..etc etc) (maybe rename the above field to fldConditionLocation for consistency of naming convention) fldConditionID FK <from tblConditionList> fldCondititonVariationID FK <from tblConditionVariation> (will be NULL unless it's a variation) fldConditionSpecialID FK <from tblConditionSpecial> (will be NULL unless it's a "Special") I think the balance of the schema is perfect and this should be enough to get me off the ground....(I think) I'll be very happy to hear any other ideas you may have come up with and/or comments about this idea, but if I've run out my 'dance card' I think I may be able to stumble along by myself now...unless you post back saying I still have it all backward :-) Now all I have to do is figuring out all the sql statements to make all this work! Thanks for the help and guidance. Mark > > > Although your observation is correct that > > > > adding more fields would allow you to add a feature using a single [quoted text clipped - 90 lines] > > Mark I just got busy. I'm writing a new module for time ticket entries and there is a lot of pressure on me to get it out as quickly as possible so that 1) the new time tickets can be used and 2) a minimum number of 2006 time tickets will have to be converted to the new format. I will get back to this thread as soon as I am able. I'm glad you took time to study what I posted in the meantime. P.S., as Kyle suggested, there is no charge for verbosity but it tends to get in the way of clear, concise thinking about the most important points. Substitute my references to Access with "Your Program." Plus, start at the top of your schema design and work your way down. You seem to be starting to grasp the "why" of additional tables so maybe your study of normalization techniques will make more sense now. Hope this helps, James A. Fortune CDMAPoster@FortuneJames.com > I just got busy. I'm writing a new module for time ticket entries and > there is a lot of pressure on me to get it out as quickly as possible [quoted text clipped - 13 lines] > James A. Fortune > CDMAPoster@FortuneJames.com Thanks , I'll be glad to see what other thoughts you come up with when you have time. Good luck with your new project Thanks for everything you've done so far. Mark I know of a shop where you can have the volumes of this thread leather-bound at a good price. HTH! Kyle |
Enable EMail Alerts
Start New Thread
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery |