Database Design Problems

[SamLis]

Hi,
I need to design a database and I'm having some problems.

Protein:
ProteinID
ProteinName
ProteinTag

a protein can be tagged on one end (N-terminal) or the other (C-terminal)
tag can be : Tag1, Tag2, Tag3, ...

so a protein can have a name, C or N, Tag but only the combo is one record
for example=
Name1, C,Tag1
Name1,N,Tag1
Name2,C,Tag1
.....
So I made 3 tables Name, C or N, Tag and a 4th table where this comes together.
but how can I enter data easily with a form?

I simplified it a bit, since I have more properties that work the same as the "tag" property.

I hope it's clear what I mean, and that you can help me or point me in the right direction

thanks,
Sam

 

[kipcliff]

Are there other data in the terminal or tag tables besides the names?

 

[SamLis]

Hi,My organization is now like this, because I don't know how to do it without repeating data? :

Tag
Tag ID
TagPositionID
TagNameID

TagPosition
TagPositionIDTagPosition

TagName
TagNameID
TagName

Protein
ProteinName
TagID


Is this correct or am I complicating things, I saw in the Northwind database that category is a field in the ProductTable, although it's repeated that way for many records.

 

[jdraw]

You may be complicating things, but we don't know anything about your situation.
Tell us in 4 or 5 lines how you would describe WHAT you're trying to do to someone next to you at McDonalds (who knew nothing about database or Access or proteins)

 

[SamLis]

ok here it is :
it's an inventory of items, but were each property of that item itself is made up by other properties?
does this make any sense?
Item is described by:
1) - Name
2) - Property1
3) -Property2


Property 1 is :
1) -Property A
2) -PropertyB

Property A can be 2 values, Property B can be x values, property 1 is a combination of A and B.

One value of property1 can appear in many items




I included the database, I hope its more clear that way.

 

[jdraw]

I have access 2003 and can not open or use an accdb file.

I don't think your description would help the poor guy at McDonalds.

If you can't describe it, it's going to be very difficult to get assistance.

 

[kipcliff]

Hello, SamLis.

Do not nest your properties. What you are trying to do is make an inventory of unique combinations of entities.

The separate tables you have made for these entities are your lookup tables.

Your Inventory table should have a record ID field as a primary key, and a field for each of the properties/entities you wish to combine. Set each entity field to lookup its data from its respective table, binding the first column (ID) and counting 2 columns (to show the property name). Set your widths to 0;1, so only the name shows.

Make your unique combination fields a multi-column index with no duplicates.

Build a form based on this table, and the controls should have the same lookups as the fields they are bound to.


TagPosition
TagPositionID
TagPosition

TagName
TagNameID
TagName

Protein
ProteinID
ProteinName

Inventory
InventoryID
ProteinID .........} Foreign keys of lookups from other tables
TagNameID ......} Multi-column index, no duplicates
TagPositionID ...}

 

[SamLis]

Hello Kipcliff,

thanks for the assistance, I see what you mean, however I'm a bit confused.
What's the benefit of lookup tables.
In the inventory table you would than have numbers repeated, you can show the name with lookup tables.
vs. one inventory table were values are repeated?

I understand that you can easily change a value in more records.
Does it also reduce size of the database?
Maybe a stupid question, but I want to learn and understand why we do the things we do.

greetings,
Sam

 

[jdraw]

This article on relational database design should be helpful
http://forums.aspfree.com/attachment.php?attachmentid=4712

 

[kipcliff]

I really couldn't put it better than the article jdraw linked. All of your data is listed once, in one place. Only the references to them are duplicated, which is normal.

You will not acquire erroneous data elements from typos if you have to select one from a list.

Database size is generally smaller, since the repeating elements (IDs) are typically the size of a long integer. The associated text entries are usually, but not always, larger.

 

[SamLis]

Hi,
I've been working a bit on the database, I included the relationship report. ( Is this what you mean?)
Am I on the right track?

I 've been working now on forms, but I've 2 questions

1) How can I make the combobox Epitope Detail, only visible if a certain value is chosen from combobox Epitope?
I found on internet and this forum related posts, but they all handle values list, while my combobox get's data from a table, do I use the ID or the value? what would be the code?

2) If you look to my relationships: Nanobodies and Experiments have a many to many relationship, In experiments, you also have to chose the technique the user used,
I want to add to my form nanobodies a checkbox that's checked if a nanobody is used with a certain technique.
This can be in 1 experiment, or 2, or ...
But from the moment a nanobody is used in a exeriment with a technique I want checkbox Yes.
I want to make 3 checkboxes on form like that for the most important techniques.

I hope this is clear,
thanks

 

[jdraw]

Did you read the article on Database design? Was it useful?

The report.pdf is a relationship diagram, and that is what we mean. There are a few general things I'd like to highlight - to make things easier for you.
*Don't use multivalued fields (Sequence etc.).
*Use a naming convention that does NOT allow embedded spaces nor special characters. Limit names to alphanumerics and underscore"_" -- you'll save a lot of extra work.
*Make sure your fields represent atomic values --single field, single meaning, single value.
*Each table should have a Primary key.
*tables are related by Primary key and Foreign key
*Do NOT use the same name for an Entity/Table and a field within that table (Expression Vector, Epitope General, Antigen)
*Tables/Entities should each deal with a single subject (Nanobodies seems to represent multiple ideas/thoughts/things.

I strongly suggest you get your data model/relationships well understood/designed before you get too deeply into coding and comboboxes, checkboxes etc. You need to get the WHAT clear before you get into the various options for HOW.

It would seem that Nanobodies exist independent of Experiments. It seems analogous to Students and Courses to me. To bring Student and Course together, as in StudentIsEnrolledInCourse, you have a new table -- a junction table -- that relates a specific student to a specific course. Other information related to the combination of student and course would be fields in that junction table.

You seem to be dealing with a subject matter that is somewhat foreign/unfamiliar to most of us. Going back to my McDonalds metaphor, could you tell us in plain English what you are doing to help us understand your environment and database?

Here are a few links to info that amy help put some of the comments into context.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

Good luck with your project.

 

[SamLis]

Did you read the article on Database design? Was it useful? Yes, very, thank you

The report.pdf is a relationship diagram, and that is what we mean. There are a few general things I'd like to highlight - to make things easier for you.
*Don't use multivalued fields (Sequence etc.).sequence is an attachment field, SpeciesReactivity I "copied" from Suppliers in the Northwind database, because I also need to be able to select multiple values from 3 choices
*Use a naming convention that does NOT allow embedded spaces nor special characters. Limit names to alphanumerics and underscore"_" -- you'll save a lot of extra work.ok, will do
*Make sure your fields represent atomic values --single field, single meaning, single value.check, except Nanobodies table (see later)
*Each table should have a Primary key.check
*tables are related by Primary key and Foreign key check
*Do NOT use the same name for an Entity/Table and a field within that table (Expression Vector, Epitope General, Antigen) will do
*Tables/Entities should each deal with a single subject (Nanobodies seems to represent multiple ideas/thoughts/things. One nanobody is a combination of different properties, that's why earlier post suggested to work with a table linked with lookup tables, as I understood?

I strongly suggest you get your data model/relationships well understood/designed before you get too deeply into coding and comboboxes, checkboxes etc. You need to get the WHAT clear before you get into the various options for HOW.

It would seem that Nanobodies exist independent of Experiments. It seems analogous to Students and Courses to me. To bring Student and Course together, as in StudentIsEnrolledInCourse, you have a new table -- a junction table -- that relates a specific student to a specific course. Other information related to the combination of student and course would be fields in that junction table.
That's why I generated the NanobodiesExperiments table = junction table

You seem to be dealing with a subject matter that is somewhat foreign/unfamiliar to most of us. Going back to my McDonalds metaphor, could you tell us in plain English what you are doing to help us understand your environment and database?

I'm working in biomedical research, a nanobody is basically a small antibody.
I hope you have heared about this? foreign proteins that enter the body ( like on viruses, bacteria) are recognized / bound by antibodies, as part of the immune respons. THis property, the fact that antibodies can recognize a specific protein among thousands of proteins makes it a very usefull tool.
Imagine that you could engineer an antibody to recognize cancer cells, or you label an antibody with fluorescence marker, you could find out where your protein is? In muscle? in nerves? ... . The possibilities are endless

We use this nanobodies in experiments, an experiment is defined by the Nanobodies used, Date, and a comment box (short description). Many to many relationship between Nanobodies & Experiments

To describe a nanobody you have certain properties (see Nanobodies table),
but change one property and you get a different nanobody,
this is acually what is happening now, I have several very similar nanobodies, but different. To have a nice overview, I thought let's make an Access Database ( I worked with access before, but just to make a db of members of a club)

Questions for the db:
1) give an overview of the nanobodies
2) filter out nanobodies by a property
3) Which techniques can you use this nanobody? -} link Experiment
4) Which Nanobodies can be used for a Technique?

thanks for the support,
Sam

 

[jdraw]

Good responses. Yes I looked at http://en.wikipedia.org/wiki/Single-domain_antibody
and
http://www.ablynx.com/en/research-development/nanobody-technology/understanding-nanobodies/

 

[SamLis]

Hi,
Exactly, ablynx uses them in disease diagnosis and treatment, we use them in biomedical research.

I included a website that shows more or less the idea what I want.
(well, I cannot include links, so google "antibody database template", you find solutions.filemaker.com)
However, what they call applications = Technique in my db, they only have a checkbox, I want to give more info on the experiment itself. Back to my original post of the day: Can I make a checkbox on a form, when people make an experiment, choose a technique, choose a nanobody, that automatically "YES" to that technique, or should I include in my nanobodies tbl a yes/no field for the technique?
I would place this checkbox on the datasheetform for each nanobody.
I know how to make a subform that can show all the experiments for that nanobody.
you probably can filter this on "Technique", but I want the user to directly see yes or no. If yes than they can check in more detail the experiments, conditions, ...)

thanks,
sam

 

[jdraw]

Just about anything is possible -- just SMOP as we used to say. I think you should watch some of those video tutorials just to get some appreciation of the steps involved.
I suggest we work to get your relationships/data model designed to meet your situation.

I saw the filemaker pro template, but don't see any quick way to bring it into Access. It's possible there are people on the forum that know Access and FMPro and they could assist/advise.

SMOP -- small matter of programming

Here's an article that may be useful -especially if you are an academic institution.
http://intl-jla.sagepub.com/content/16/1/82.full

What is your status at this time -- do you have a lot of data; just starting; ???

I haven't found anything free that works with Access, but will keep looking.

 

[SamLis]

Hello,

I included the filemaker just to give an idea how my form would look like.
I know that there are a lot of LIMS out there (Laboratory Inventory Managment System), but those are very expensive. You have a couple of free ones, but then you are limited bye what you can do/store.
This is something for me personally, since I 'm responsible for the nanobodies.
It started as a small project, but due to the success the numbers of nanobodies is rapidly extending.

Access seemed for me the the most "easiest" tool.
Anyway I can upload the database "so far"

thanks,
sam

 

[jdraw]

I have acc2003 and can NOT read/open an accdb formatted database. You can save a copy in mdb format and post if you could.

 

[SamLis]

Hi,
I had to remove one field because it was an attachment field, I also had to change the field speciesreactivity, since it was a multivalued field (like I explained earlier)

Those 2 types of fields are not supported in 2003.

 

[jdraw]

OK good.
I'm returning a modified mdb in a zip that has the relationships repositioned for readability. Also, I added a form and a module. The DocumentTables proc in the module creates a table called data_dictionary and populates it with the fields and descriptions. The Form simply has 1 button to Document the Tables in the database. The button click runs the procedure, which rebuilds the table from scratch.
It isn't that elegant but does the job. You can use these as you see fit. You can always create some queries or reports/forms to generate specific info.

If you have documentation on your tables, you could paste some meaningful text into the Table Description property. I think it would be a useful exercise for you to document the table descriptions so that I have some "feel" for the data and can help (hopefully) with filling out the model.

I don't know if you have dealt with "business facts", but that also would be a meaningful exercise. There are some "examples" at databaseanswers.org These are usually recorded with the data models. As you will see, some are more useful than others.

There is a data model here
http://www.databaseanswers.org/data_models/laboratory_test_systems/index.htm and the "business facts/specifications' here
http://www.databaseanswers.org/data_models/laboratory_test_systems/facts.htm