 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / November 2006one to one relationship help and database design question
hello i have a database where i am trying to store information about 5 different types of machines by using a table that holds the common information, with relationships to tables with the uncommon information in a one to one relationship. there is an image of the relationship structure here: http://img73.imageshack.us/my.php?image=relationshipsnov6mx2.jpg the area in question is the machinelist table and the subdata tables. each subdata table is a different type of machine. the problem with this arrangement is this; each machine can have one record in each of the five tables! that means one machine can be all five types. this is not what i had intended with this structure. i was hoping that there was a way to cause the MachineID to only be used once with one of the five types of machines. meaning that one record in the machinelist table, has only one corresponding record in one of the five subdata tables. how can i do this? On Nov 6, 10:09 pm, DawnTreader <DawnTrea...@discussions.microsoft.com> wrote: > i have a database where i am trying to store information about 5 different > types of machines by using a table that holds the common information, with [quoted text clipped - 8 lines] > meaning that one record in the machinelist table, has only one corresponding > record in one of the five subdata tables. See this example of subclassing, which uses a Vehicle superclass and subclasses of SUV, Sedan, etc: http://groups-beta.google.com/group/comp.databases.theory/msg/2f2e1ff43b17682c "use a compound candidate key (vin, vehicle_type) and a constraint in each sub-class table to assure that the vehicle_type is locked and agrees with the Vehicles table." Jamie. -- Hello hmmm. that is just a little over my head. i think it is sql that is shown there. but how do i implement that in an access database? where do i put the "code" or what do i change to create this set of checks? thanks for the pointer, can you elaborate? > On Nov 6, 10:09 pm, DawnTreader > <DawnTrea...@discussions.microsoft.com> wrote: [quoted text clipped - 24 lines] > > -- > i think it is sql that is shown > there. Sure is :) Polite hint: you can't get far in DBMS-land without knowledge of the SQL language. A quick/simple tutorial may be found here: http://sqlcourse.com/intro.html CREATE TABLE is introduced in lesson 4. > but how do i implement that in an access database? where do i put the > "code" or what do i change to create this set of checks? Allowing for typos and ellipses, the SQL DDL can be used in Access/Jet while in ANSI-92 query mode e.g. using an ADO connection. The CHECK constraints may be implemented as (field-level) Validation Rules. The following VBA code creates a new database (C:\DropMe.mdb) to contain the tables with CHECKs and Validation Rules: Examine the structure, noting that you can only create vehicles of type SED or SUV, that a SUV cannot be inserted into Sedan and likewise a SED cannot be inserted into SUV: Sub JCelkoVehicles() ' Create database Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\DropMe.mdb" ' Create tables With .ActiveConnection .Execute _ "CREATE TABLE Vehicles ( vin CHAR(17) NOT" & _ " NULL PRIMARY KEY, vehicle_type CHAR(3)" & _ " NOT NULL, CHECK(vehicle_type IN ('SUV'," & _ " 'SED')), UNIQUE (vin, vehicle_type) );" .Execute _ "CREATE TABLE SUV ( vin CHAR(17) NOT NULL" & _ " PRIMARY KEY, vehicle_type CHAR(3) DEFAULT" & _ " 'SUV' NOT NULL, CHECK(vehicle_type = 'SUV')," & _ " UNIQUE (vin, vehicle_type), FOREIGN KEY" & _ " (vin, vehicle_type) REFERENCES Vehicles" & _ " (vin, vehicle_type) ON DELETE CASCADE ON" & _ " UPDATE CASCADE ); " .Execute _ "CREATE TABLE Sedans ( vin CHAR(17) NOT NULL" & _ " PRIMARY KEY, vehicle_type CHAR(3) DEFAULT" & _ " 'SED' NOT NULL, CHECK(vehicle_type = 'SED')," & _ " UNIQUE (vin, vehicle_type), FOREIGN KEY" & _ " (vin, vehicle_type) REFERENCES Vehicles" & _ " (vin, vehicle_type) ON DELETE CASCADE ON" & _ " UPDATE CASCADE ); " End With ' Create Validation Rules Dim jeng Set jeng = CreateObject("JRO.JetEngine") jeng.RefreshCache .ActiveConnection .Tables("Vehicles").Columns("vehicle_type") _ .Properties("Jet OLEDB:Column Validation Rule").Value = _ "IN ('SUV', 'SED')" .Tables("SUV").Columns("vehicle_type") _ .Properties("Jet OLEDB:Column Validation Rule").Value = _ "='SUV'" .Tables("Sedans").Columns("vehicle_type") _ .Properties("Jet OLEDB:Column Validation Rule").Value = _ "='SED'" Set .ActiveConnection = Nothing End With End Sub Jamie. -- Hello ok. i know you are trying to help, but this is way beyond me at this point. there is no way i am ready to start with all these fancy initials. i took a look at the relationship window in access. thats cool. but if i cant make it without learning a lot of programming, i am hooped. i am currently working on a database that is of urgent need. learning the indepth stuff you are telling me wont help me, right now. now i am greatful for your interest in helping me, but how am i going to implement that in what i have currently? have a look at this: http://img73.imageshack.us/img73/8365/relationshipsnov6mx2.jpg i need the machineID to show up only once in the whole database. machine # 42 can only be a compressor, and so that machine ID cannot be allowed to show in the dispenser table. as my database currently stands there is no way, except through interface, to ensure this. i understand that your method appearantly causes the situation i want, but how do i create that situation in my current database? i cannot create a whole new database, i have hours of data input already done to this and would like to keep everything intact and i have limited time constraints on getting this implemented. will what you propose cause me to have to do a lot of additional coding to implement it in the interface? what happens when i create a form based on one of the tables? i am sorry if i sound frustrated, my boss would like to get this done so we can use it to do the business that we are supposed to be doing. he doesnt always understand the fact that it takes time to develope this stuff. > > i think it is sql that is shown > > there. [quoted text clipped - 82 lines] > > -- > i know you are trying to help, but this is way beyond me at this point. > there is no way i am ready to start with all these fancy initials. i took a [quoted text clipped - 26 lines] > can use it to do the business that we are supposed to be doing. he doesnt > always understand the fact that it takes time to develope this stuff. Sorry, I don't have the information I would need to solve your problem. I'd need to make some assumptions... What is MachineType? I'll guess it is the following set of values, reflecting the names of your five subdatatbl- tables (I hate the prefixes!): {'Compressor', 'Storage', 'Dispenser', 'DecantingPost', 'FillPost'}. However, such a unique set of types would make your MachineTypeID redundant - TypeID is an oxymoron! - and using the type (text) in your other tables would increase readability of your data. So I assume MachineTypeID is an 'uniquifier' autonumber to fake an exposed surrogate, another design choice which I do not advocate but I'll go with it, just for you... In a nutshell, I think you need to complete these steps (or similar): 1) To each of your five subdatatbl- tables add a MachineTypeID column of to reflect the column MachineID on table tblMachineList: I'll guess INTEGER (Number, Long Integer) NOT NULL (Required = True/Yes). Don't make it an autonumber, though. 2) To each of your five subdatatbl- tables add a validation rule to ensure the MachineTypeID corresponds to that table; assuming {MachineTypeID=42, MachineType='Compressor'}, the Validation Rule on table subdatatblCompressor column MachineTypeID would be =42 [Do you see how ='Compressor' would make this Validation Rule more dolphin-friendly?] 3) UPDATE each of your five subdatatbl- tables to populate the new MachineTypeID column e.g. UPDATE subdatatblCompressor SET MachineTypeID = 42; How you configure the Query Builder UI thing to write that SQL for you is a mystery to me <g>. 4) In your main tblMachineList table replace the single-column PRIMARY KEY (MachineID) with a composite PRIMARY KEY (MachineTypeID, MachineID) [note the left-to-right column order, MachineTypeID then MachineID, is significant; I know how to specify this in code but not using the UI - but then I'm not Access UI expert!] 5) Change the FOREIGN KEY ('Relationship') from this: (MachineID) REFERENCES tblMachineList (MachineID) to this (MachineTypeID, MachineID) REFERENCES tblMachineList (MachineTypeID, MachineID) IIRC this involves dragging the referencing column MachineTypeID (i.e. the one in the subdatatbl- table) and dropping it on the referenced column (i.e. the one in the table). 6) In each of your five subdatatbl- tables replace your single-column UNIQUE (MachineID) with constraint ('Index') with UNIQUE (MachineTypeID, MachineID). I'm not entire sure but maybe if you specify a one-to-one when creating the 'Relationship' these constraints get created for you (and is that is supposed to be a good thing?!) Do you get the impression I don't know my way around the UI <g>?! HTH, Jamie. -- Hello again Jamie thanks for coming back again. :) this actually makes a little sense to me when i first scan it. what i have done is made one table that stores all the characteristics that are the same for all our products. we are trying to firm up the data structure and build an interface to use this database in actual day to day situations. we build natural gas compressors, dispensers and other equipment for use at a customers filling site. at each site there is a possiblity of having more than one compressor, dispenser and other equipment. the thing is that they all have some data in common. hence the reasoning for tables that store the data that will be similar and seperate tables for the types of machines. the machine type problem you mention i had never realised until you said it. i feel so childish and stupid now... so if i follow the steps you mention i will remove redundancy, and i will end up with a set of relationship like your code database? how will this affect things like lookup combo boxes. not ones created by the lookup in the tables design, but ones like the forms combo box creation wizard? {i will assume now that you dont know, because you aren't and access UI expert.} :) i am going to go through this procedure and see what happens in a duplicate test database. if i have further questions i will get back here with them soon. thanks for your help, and your willingness to 'stoop' to my amatuer level of knowledge of access. > > i know you are trying to help, but this is way beyond me at this point. > > there is no way i am ready to start with all these fancy initials. i took a [quoted text clipped - 100 lines] > > -- > if i follow the steps you mention i will remove redundancy, and i will > end up with a set of relationship like your code database? Think 'data integrity' (redundancy is different consideration). Implement the steps and you should have effective constraints that prevent, say, a compressor being inserted into the dispensers table. > thanks for your help, and your willingness to 'stoop' to my amatuer level of > knowledge of access. I don't think like that! Horses for courses... > how will this affect things like <<help! what do such things mean?>> > {i will assume now that you dont know, because you aren't and access > UI expert.} :) ...you are correct <g>! I suggest you repost any specific questions in one of the microsoft.public.forms groups, were some real experts are lurking. > the machine type problem you mention i had never realised until you said it. > i feel so childish and stupid now... That wasn't my intention :( You were supposed to merely slap your forehead, say something like 'Doh!' or 'S**t, you're right!' and schedule in a fix for the flaw. Jamie. -- Hello again [note the left-to-right column order, MachineTypeID then MachineID, is significant; I know how to specify this in code but not using the UI - but then I'm not Access UI expert!] left to right and significant, why? > > i know you are trying to help, but this is way beyond me at this point. > > there is no way i am ready to start with all these fancy initials. i took a [quoted text clipped - 100 lines] > > -- > > note the left-to-right column order, MachineTypeID then MachineID, > > [in the PRIMARY KEY declaration] is significant; > > left to right and significant, why? Briefly, physical ordering on disk ('clustered index'). In Access/Jet, the 'primary' index (the PRIMARY KEY constraint or in its absence the chronologically first created UNIQUE constraint) determines the physical ordering when the file is compacted (subsequent rows are inserted in date/time order). Think paper copy telephone directory. It can have only one physical order (obviously). If it were ordered on telephone number, would this be useful? No. It is ordered on last_name then first_names e.g. PRIMARY KEY (last_name, first_names). The left-to-right order is significant here because it is not the same as (first_names, last_name) first_names then last_name, being an entirely different and less useful set ordering. Physical ordering on last_name first will particularly favour tasks such as, 'Grab me all the people who's last name begins with the letter 'p'. I've assumed having your 'main' table physically ordered on machine type will be beneficial e.g. to GROUP BY queries. There is a counter argument, and a good one, that physically ordering on a random integer (e.g. autonumber) column will improve concurrency (although with Jet 4.0's record-level locking this argument is less strong). I assumed you wouldn't be able to make a judgement on concurrency considerations because, well, there doesn't seem to be anything relevant in the Access documentation! This is a bit of a controversial issue because historically the Access documentation said to say that Jet does not 'support' (operative word) clustered indexes (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/o utput/F1/D2/S5A274.asp) whereas the more recent/detailed documentation confirms that a concept of 'non-maintain clustered indexes' does indeed exist (http://support.microsoft.com/default.aspx?scid=kb;en-us;137039 and http://support.microsoft.com/default.aspx?scid=kb;en-us;209769). Excuse me while I prepare to be flamed once more for daring to speak the truth... <g>. HTH, Jamie. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.