Don't Quite Understand 1

[isaidi]

thread700-875964

I have a similar problem the the refrenced thread above. IT would help me if i can understand the last part of that thread.

How would you store the value's of the attributes.
Let's say you have different attributes for the Tumors, and the data types of these tumours are different. If you learn to diagnose new tumors in the future with new attributes, you will have to add new columns to the attribute table??


my problem is exactly the same where each object in the tumour table can be associated with multiple attributes of different data types. I couldn't avoid having multiple tables for each Tumor, but this gets really big and hard to maintain...

Would some one shed some light on this ?

 

[isaidi]

Anyone ?

 

[dhookom]

I would create tables of
- tumours
(one record per tumour)
- attributes
(one record per attribute of tumours)
Also store the data type of the attribute
and possibly hight and low acceptable values
- tumour attributes
(one record per tumour per attribute)
tumourID, AttributeID, AttributeValue

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[isaidi]

Thanks dhookom!

Would this really work better with my forms and queries though? I expect to make forms to enter information about the Tumors, and I may have attributes that are CHeck-Boxes (Boolean), Numbers, string...

What data type would I make the AttributeValue field. Text?
I would have to store all attribute values as Text and perform some coding in the form to convert the data type?
Perhaps look up the data type in the attribute table and conver the text accordingly

I am not a database design guru, but it just seems that this kind of design would increase the complexity of the Data Entry interface. COnsidering I would like the user to Enter all information regarding a specific Tumor in One Screen.

I am new to this, and i have to decide in my design right now if i will should break up the design like mentioned above, or keep my multiple table design and just extract all the common information. Which would help me in the long run?

 

[lespaul]

Storing the data and presenting the data are two different processes. Read the link below for information on storing the data.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[isaidi]

Thanks Leslie!

I have read several of your SOlutions in other threads, I also downladed and read that document from your other threads, very well summarized.

I guess when it comes to coming up with the design I seem to get stuck at resolving my multiple tables problem .

my database is loggicaly identical to the TUmor Exmpl, however, instead of Patients I have Equipment and Instead of Tumors I have Exhibits.
Only One Equipment can be related to several exhibits. but not vice versa. therefore 1::Many...

I have about 50 types of exhibits that must be completed for about 8000 devices, each exhibit contains almost 20-40 fields and they can be Text,Boolean and Numbers. THe names of the fields in each Type of Exhibit are also different.



This is what i have so far:

tblEquipment
-------------
EquipID
...other info about the equip

tblExhibits
------------
ExbtID - PK
EquipID - relates an Exhibit with an Equip
ExbtCode - Identifies what kind of Exhibit
EbtRev
EbtBy
ExbtDate
..etc..other Common info to all exibits...


tblTypeOfExhibit (Listing all types exhibits)
------------------
ExbtCode - PK
ExbtName
ExbtDesc
ExbtPages
...etc...


tblEquipment (1:Many) tblExhibits
tblTypeOfExhibit (1:Many) tblExhibits [each exhibit can only be of one type]


now the problem is the fields..
I was planning to use multiple tables for the remaining fields, but you are suggesting the follwoing?

tblFields (All fields of all Exhibits, no values)
-----------
FieldID
FieldName
FieldDataType

tblExhibitFIelds (Relates FIelds to an Exhibit TYpe)
--------------
ExbtCode
FieldID
** I can't add Value here, since for a given ExbtCode, I can have several 100 exhibits with different values.


now how would I store the values of each field.. I would like to avoid a table for each exhibit, since i would then end up with 50Tables.. , plus i would have to add a new table for new exhibits...

 

[lespaul]

why don't you explain a little about what this database is suppose to do? and a little more details about what type of information you need to store about the exhibit fields...is the equipment stuff that's needed by exhibtors?

It helps (me at any rate) to have a little understanding of what you are actually working with.

 

[isaidi]

essentially this database is just supposed to store the exhibit documents.
an Exhibit is just a document that is filled out and completed for each equipment after it is installed. So each Exhibit is directly related to an Equipment. It is a Quality Asurance/Control Check, but it will have varoius information about the equipment as well.

Different equipment requires different Exhibits, but most exhibits have common information such as.. Who filled it out, when it was filled out, was it accepted?

Equipment could be Cables, Motors, etc... I don't care about storing much information about the equipment, all i need to be able to do is identify it and associate it with a specific Exhibit.


each exhibit can have upto 50 fields.. for example you may have to fill out the Voltage Rating of a Device, Maximum Temperature, CHeck boxes for installation, Model#, Drawing Ref#, and also Test Results like Temp, SPeed, Voltage readings of a device under certain conditions. These exhibits/Tests Asure the quality of the Equipment and it's installation.

I see where you might lead this to, I thought about decomposing the Exhibit Tables and moving all the information about the equipment to tblEquipment, if it is directly dependand on that equipment. This may be possible for some, but there are fields that i can't move Like Test Results, as they related to ExhibitID only. I beilve they still meet 2nd Normal FOrm, as each field is directly related to ExhibitID, which inturn relates to a single Equipment.
I would like to make it easy in the future to Add/Change Exhibit Forms, so the fields my change and new information might be required now, about the euqipment, or about the test.



thanks

 

[lespaul]

Ok here's what I can extract:

Each piece of equipment has one or many exhibits that must be completed.
Each exhibit has many elements.

I'm assuming that each Exhibit is defined and you know all the elements that go to each Exhibit.

Exhibits
ExhibitID
ExhibitName (Document ID or something?)


Elements
(list of ALL the elements that could be on ANY exhibit)
ElementID
ElementDesc
(this is where you would put: Voltage Rating, Maximum Tempature, etc.)

ExhibitElements (this table links the elements to the correct exhibit)
ExhibitID
ElementID

Now you have the Equipment:

Equipment
EquipmentID
Name

EquipmentExhibits
EEID
EquipmentID
ExhibitID
ElementID
Value (this is where you would store the answers to the elements)

then optionally:

RequiredEquipmentExhibit (you could use a table like this to standardize which Exhibits are required for each type of equipment)
EquipmentID
ExhibitID

Hopefully this will point you in the right direction!! Feel free to post back with questions!

I treated this the same way I would treat a Survey database, since what you are doing is very similar. Look around at some posts and try to find Duane's survey database, it might be very helpful to you.

leslie

 

[isaidi]

This looks like it might work for me.

I might do this little change to allow adding common fields to EquipmentExhibits that are independent of Exhibit Type and avoid repeating the common Info for every ElementID/Value...

tblEquipmentExhibitElements (Junction Table to tblElements)
------------------
EEID -PK
ELementID -PK
Value
ExhibitID


tblEquipmentExhibits (add the common fields)
--------------
EEID
EquipmentID
Date
CompletedBy .
.etc.. other common fields independent of exhibits.....


this way i wouldn't have to repeat the common fields for every element/Value association.


I also moved out ExhibitID to the Elements Junction table, since the information in tblEquipmentExhibits is independent of Exhibit Type. The elements will depend on Exhibit Type.

Do you think this change will be good ?


With this design, i will just have to figure out how i can map the elements to the forms properly, since controlsource for textfields usually maps to Column Header Name...

with queries i can extract a table looking like this:

| Element Name | ELement Value
-----------------------------
TEMP 25
Voltage 300V
.... each record will be an element.. which will be associated with one single EquipmentExhibitID

as oppsed to the usual way of

|Temp | Voltage
---------------
25 300V

One record is associated to one EquipmentExhibitID

I like having one record per exhibit...
I see that Duane's survey database used a continuesForm "where" the response matches the questionID of the currently displayes question. Clever! but doesnt work for me as each of my Exhibits is a specific Template and can only be in SingleForm. Perhaps i should ask this question in the Forms Forum?

 

[BobRodes]

One thing about your model, lespaul: does elementid really belong in the equipmentexhibits table? I would have thought not.

Bob

 

[isaidi]

I agree Bob,

I have made the association between an Element and an EquipmentExhibit in a seperate Junction table, since there are about 50 elemnts or so to a given EquipmentExhibitID

thanks for confirming that for me.

It seems like this Table Design is going to work smooth for me. I am just working out the details of interfacing to it.