Database Design Problems

[SamLis]

hi,

When I look at tblNanobodies I notice that
TagId has the same value as ExpressionId <----Is that just coincidence in your test data?

this is coincidence of the test data, it can be different/independent of each other.
tblNanobodies vs tblNanobodyFix

Each Production generates a nb.
Has a name, Expression, TagPos, and Tag,
linked to the production we know the Epitope, SpeciesReactivity.

now in the lab we "play "around and modify this nb's, but we can only change certain things : ExpressionVector, TagPosition, Tag.
We cannot change SpeciesReactivity, Epitope , these are dependent on the Name.
Maybe the term Name is not chosen properly?
if you compare in my records Nanobody ID 1 & 4,
they have the same "Name", and will recognize the same protein (Epitope), but what techniques we can use them will be different due to the different Tag, ExpressionVector.

ID 1 has also a Expression Vector and TagPos and Tag, this are the defaults
ID 4 is an adapted ID 1.
If I want to search my nb's on the Antigen = EpitopeGeneral, I want both to pop up,
than I can narrow the filter on what technique I want to apply.


Sam

 

[SamLis]

hi,

I made some forms, based on the query and tables, to see if it's workable and I see all the info I want.
It seems to be moving forward, any idea if this tblNanobodyFix is the right way to go?
In preventing that people might select an epitope that cannot be, since the production (see previous posts)
-Sam

 

[jdraw]

I still have not seen definitions/description that clearly show that Nanobodies and nanoBodyFix are different things. I think getting that sorted is a necessary step before getting too far into Forms.

Write a simple description of each. If the definitions don't clearly show a difference, then you may have to merge these tables. You may also want to describe why these are different, or How you can tell a Nanobody from a nanobodyFix. At some point they will be shown to be the same, or clearly different.

I have just looked at your latest database.

I see 3 nanobodyFix records and 4 nanobodies -- I think we should determine or clarify why these are different/same. I'm sure you know, and you may have told me, but let's get definitions for these.

I was somewhat surprised to see only 3 records in your Form - but that is because of NanobodyFix. Can you tell me why there are 3 records and not 4?

 

[SamLis]

Hi,
I just made this tblNanobodyFix, as you can see in the ppt, there were attributes dependent on each other in one table? I thought that this was to be avoided.

Further I made frmOverview were you see 3 records, when you click "open", it opens the corresponding frmDetails?

-Sam

 

[SamLis]

Hi,

NanobodyFix is a part of Nanobody, I don't know if that makes sense to you?

To describe a nb :
Name;Epitope,EpitopeDetail,Production,SpeciesReactivity,ExpressionVector,TagPosition, Tag.

However Name;Epitope,EpitopeDetail,Production,SpeciesReactivity are dependent, if you know Name you know the rest.
We modify these nb's see record1 vs record4.
But some attributes we can not modify, those I collected in TblNanobodyFix.
So what we can change are ExpressionVector, TagPosition and Tag and Aliquot.

That's why NbFix has 3 records, Nanobodies has 4, record 4 is a modified record 1.
NbFix are fixed, these are result of a Production, there will be only more records here when we do a new "Production"
While Nanobodies we can make 10 different variants which share a common nbFix, meaning Name,Eptiope,SpeciesReactivity,...

thanks,
Sam

 

[jdraw]

Still not clear, but it's not my field.
So nanobody X can have only 1 TagPosition and can only have 1 SpeciesReactivity

It seems to me (the great unwashed) that you have something before nanobody
eg

I take "thingy Z" and do something that adds a Tag Q in TagPosition B and it has SpeciesReactivity (HUMAN) and that sort of represents an Nanobody.

Nanobody seems to be a Relationship as compared to a simple Entity.

Can you give more examples -- the simpler the better, until I start to understand or we can say Yes that's it.


Have to go out.

 

[SamLis]

Here are the examples,
I also included some error examples, or that should give an error.
Indeed Nanobody X is a combo of several attributes, however not every combo is valid, that's the point.
I don't know if the extra table is the solution for this, or keep them in one table and lock some fields?

thanks,
you've been a great help,
Sam

 

[jdraw]

I have opened your book1.xls and will try to understand it.

I have done a lot of googling in addition to the questions posed to you, and I can not get a clear understanding of the pieces.
I really do not like (from a database point of view) that Nanobody name is the key to understanding Nanobody and nanobodyFix.

I found an interesting presentation at
http://pigtrop.cirad.fr/content/download/9836/50962/file/Presentation Serge Muyldermans.pdf

but, although it has relatively simple graphics, it is very much laced with the jargon/technical terms of this subject matter.


I realize it is scientific subject and requires precision and terminology. (Fab- scFv ???)

The sorts of things I find confusing are

you start with some special proteins and you manipulate things and get a nanobody that has certain properties; depending on the nanobody and some properties it exhibits various characteristics when reacting with various antigens;

a nanobody name, in your case, represents "things" that can have different properties
(tag location, tag...)

I have a simple picture in my mind -- and I'm trying to fit the nanobody stuff into a "template"

the automobile production line (let's use Ford as an example)
-make - Ford
-model - Fusion
-options -
---- doors: 4dr /2dr
---- engine: v6a/v6b/ 4
---- trans: automatic/manual 4sp/5sp
---- wheels: 16" steel
---- color: red/white/green/black/brown.....
...etc etc


Reality::
Fusions are all Fusions
regardless of engine or trans, or wheels or Number of doors

but each would have a unique VIN.
In this arrangement, each vehicle is unique (VIN). You can group the vehicles by
number of doors, or transmission, or color or any combination.

I'm sure most of it is terminology ( and probably the specificity) of the subject matter, but I'm not seeing the relationship of the "things" clearly.

Comments welcome.

 

[SamLis]

Hi,
I made some adjustements,

I have opened your book1.xls and will try to understand it.

I have done a lot of googling in addition to the questions posed to you, and I can not get a clear understanding of the pieces.
I really do not like (from a database point of view) that Nanobody name is the key to understanding Nanobody and nanobodyFix.

I found an interesting presentation at
http://pigtrop.cirad.fr/content/download/9836/50962/file/Presentation Serge Muyldermans.pdf

but, although it has relatively simple graphics, it is very much laced with the jargon/technical terms of this subject matter.


I realize it is scientific subject and requires precision and terminology. (Fab- scFv ???)

The sorts of things I find confusing are

you start with some special proteins and you manipulate things and get a nanobody that has certain properties; depending on the nanobody and some properties it exhibits various characteristics when reacting with various antigens;

a nanobody name, in your case, represents "things" that can have different properties
(tag location, tag...)

Nice presentation ( actually these are the guys that we collaborate with, "Production"
they provide us the Nanobodies VIB Report.
However they investigated how to make Nanobodies vs normal Antibodies, ther these terms Fab, ... play a role, these are the parts that are present in one but not in the other.
However for our db this info is irrelevant, we only work with nanobodies.
To use your analogy, it's like comparing a bike to a car, if you want to make a db about Fords, you don't need to know about bikes although both have a steern wheels, and an engine

I have a simple picture in my mind -- and I'm trying to fit the nanobody stuff into a "template"

the automobile production line (let's use Ford as an example)
-make - Ford Nanobody
-model - Fusion Name: Nanobody 20/Nanobody 23
-options -
---- doors: 4dr /2dr Antigen: GFP/Cherry
---- engine: v6a/v6b/ 4 Tag: Ha-6His/StrepII
---- trans: automatic/manual 4sp/5sp EpitopeDetail:
---- wheels: 16" steel
---- color: red/white/green/black/brown.....
...etc etc

You can easily replace the names/terminlogy, my problem is now, not every combo is valid; for instance Ford Fusion can have a V6, but if you have V6, you need 18" steel. In my situation When you choose Fusion, you can only have 4 drs, manual,
you can only choose the engine.

Now this starts to sound weird talking about cars, forget about the science, this is basically it, If you choose a model, you can only change some things, other are fixed,
hence the tblNanobodyFix
And even if only change the color it would be a new VIN
Reality::
Fusions are all Fusions
regardless of engine or trans, or wheels or Number of doors

but each would have a unique VIN.
In this arrangement, each vehicle is unique (VIN). You can group the vehicles by
number of doors, or transmission, or color or any combination.

I'm sure most of it is terminology ( and probably the specificity) of the subject matter, but I'm not seeing the relationship of the "things" clearly.

Comments welcome.

 

[jdraw]

Thanks for responding.
I agree that some "options" are only available in "specified combinations".
we often see (the chrome package, or the performance package, or road handling package) ==== so the company offers combinations of options; not an unlimited list.

In database terms I see these as junction tables or relationships that are relevant (valid) to a specific ("make/model") "Nanobody 20".

Do you have a list of possible values for each of these?

Tag
Antigen
EpitopeDetail

Can we identify which combination of these apply to which Nanobody (model eg nanobody 20)?

Where do SpeciesReactivity and ExpressionVector fit?

 

[SamLis]

Hi,

This is what I tried to show you in the Excel file.
Tag, TagPosition, and ExpressionVector are the "options" we can choose freely for every "car".
Antigen = epitopegeneral, EpitopeDetail, Production, SpeciesReactivity are not flexible, one value of these per Model/Name.

with Antigen dependent/ relationship on the Production as we see in the RelationshipReport.

-Sam

 

[jdraw]

I have been looking at your test records and have tried to rework some of the materials.

I have populated some of your tables based on the test data. And created a new table to deal with the "model" from the auto analogy.
I also created a Form to deal with the various valid combinations. This then required another table I called it tblNanobodyBase. I also created a table called
tblValidpropertyCombos that stored the valid combinations of all the options based on your test data.

I am attaching a few jpgs for your comments/review/thoughts.

The relationships is how I saw your test data serving as the various valid options (V6 18" wheels based on your data).
The tblValidPropertyCombos identifies all the options as distinct records/pakages.
Using queryToShowValidCombinations lists those (tblValidPropertyCombos) out in readable format. The design for the query is in a jpg.

You'll note the Valid combinations match your test data.

I have not dealt with the Production/experiment issues yet.

I'd like to hear your comments/concerns of this part do far.

 

[SamLis]

Hi,

The Antigen should be linked to production that is linked to a Nanobody.
If I understood it in this valid combo tbl you listed all combo's?
What if next year we want to try a new Tag? We need for each fill in the combo with this new tag?

What is OptionName? Is this NanobodyName, than it can not have different EpitopeDetails

-Sam

 

[jdraw]

If you want a new Tag, or a new SpeciesReactivity or whatever, you add it to the specific table eg new tag --> Tags table; new speciesreactivity --> SpeciesReactivity table
then you add a new entry(s) in the ValidPropertyCombos table.

I'm trying to piece together a model based I what I understand from you and anything I can google. At this point I have no idea what a production is so I'm not automatically relating it to anything. What i need from you is a description/explanation of a) what a production means in terms I understand, and b) some rationale as to why a Production is related to an antigen to a nanobody. We're just not in the same wavelength as far as the terminology goes.

An option name is similar to a Performance Package in the automobile analogy.
These properties/attributes come as package (v6, 18" wheels and better shocks).

One entry for EpitopeDetails, as I recall , is blank/empty. So it may or may not be present. I got that from your test data -- the values that were good/valid.

 

[SamLis]

Hi,

I'm still not sure about the validcombochoice form, since actually it is not a choice but a fact.
Let's go back to the car :

Ford assembly line can only produce 4 doors (antigen) on a day (immunizationdate) , so all cars that roll of the line have 4 doors.THe line provides a report (VIBReport) with there results. More; each model can have only one type of transmission (epitopeDetail)
Result : Ford Focus 4 doors manual 4sp
Ford Fiesta 4 doors automatic
these car have an engine (Tag) and a color (Expressionvector) and wheels (Tagposition) but these are the default and are the same for both cars.

Now another Day they make 2 doors,
Ford Escape 2 doors manual 4sp
(notice they can not make a Focus 2 doors)

so if a customer wants to buy a Focus (Engineer a new nanobody),
they are obliged to take a Focus 4 doors manual 4 sp, they can change the engine, color and wheels, never the doors or transmission.

- If you link the ProductionID to a car you know the doors
- from the model you know the transmission.

Let's take this car racing (Experiments)
you have different type of races (Techniques) : Dirt/Circuit/Street
Each Race is one type, but more cars can enter the race
vice versa one car can enter many races.
Each race you have a driver (user) and a setup (concentration) for that car in that race, and a result; again for that car and that race.
The same car can or not require a diff. setup in another race

I hope it's more clear now,
Sam

 

[jdraw]

Good stuff, now we're getting into some of "what" of your environment.

It isn't that

validcombochoice form, since actually it is not a choice but a fact.

represented a choice. What that was doing was "loading an authoritative table with the only set of values (combinations of fields and values) that were "Valid" according to your test data.
It isn't a choice for the user. It was the "admin" set up to define what (combinations of fields and values) were acceptable. At any time during any interaction, anything selected, had to be a validated "package of acceptable fields and values" - otherwise you have a "potential error condition" and could "interrupt activity with a message. All just to ensure the data matched your "valid/good".

I'll take more of a look,now that you have identified more.

Later...

 

[jdraw]

In the attached zip are 3 files

- a word doc that highlights some of our recent posts. I've tried to make a summary (in green font) and the (dark red) identifies Entities. At the end of the document in orange font are a couple of points that seem confusing - if you can clarify, please do.
Based on your posts I have equated the car and nano related entities so, if the car analogy info is correct and complete, conversion to nanobody-related entities and relationships should be relatively straight forward.

-an mdb file showing the relationships of the car analogy. I have populated tables based on your comments in posts. Also included are a few queries to show info about production Runs, some info about races, and some info related to Fiestas produced.

- a jpg of the relationships of the car analogy which hopefully covers off all the info you provided/clarified in extending the car analogy.

If there are changes to be made, please identify them and I will adjust the model.

 

[SamLis]

hi,
I haven't had much time, so a first look,
The "Production" leads to a model, not a car.
You stated it correctly in the green writing, but on the jpeg , it has a relationship to tblVehicle.
If you change that, you have the db I posted on 16 oct 2012 DatabaseNbs2_jd

the only confusion maybe exist with the different between model and vehicle, or what I call NanobodyFix and Nanobody.

It's like an assembly line produces 100 x ford Focus,
they all have an engine and color default. But you can change the engine and color, but not doors and transmission.
Every Focus will be 4 doors Manual 4 sp, What I call production is generation of the model, all changes afterwards like color are not production, in my db.
Of course if I only Change one of the 100 cars, I don't want 100 reords but 2,
the default and the changed.

But we are getting there,

-Sam

 

[jdraw]

I've just looked at your response and am glad "we're getting there."

I was dealing with the analogy and your expansion to races etc. Vehicles or individual cars are in races, so I was working to create vehicles/cars. I'm not quite following the production leading to a model. I can see a particular production run making a whole bunch of 4 dr, 4 sp manual trans models.

The other part I'm not following is

It's like an assembly line produces 100 x ford Focus,
they all have an engine and color default. But you can change the engine and color, but not doors and transmission.

It's the 100 x Ford Focus in a run all with the same engine and color default.
But you can change the engine and color?

??Same color default - but you can change the color??

What I call production is generation of the model, all changes afterwards like color are not production,

Are you saying that a "model" is an interim "thing" (semi-finished) to be followed by other necessary but not production line activities (engine,color, wheels)?

Please clarify/comment and I'll adjust the "car" picture as best I can until we get it to match your "business".

 

[SamLis]

hi,
What I mean with production leads to a model is the following,
When Ford Produces Focus :

Focus 4drs Manual 4 sp Grey, 1.6 TDi, only this is a consequence/product of a "Production", with Grey, 1.6 TdI being defaults.
Another "production" can generate Escape 2 drs Automatic Grey, 1.6 TDI.
If you want a Focus Red 2.0 TDi, that is a possible vehicle, but it is not a result of a Production.
However this Red car will automatically have 4drs Manual 4 sp.
Since all Focus cars have this because they came from one production.

so model is not really interim thing, it has a color and a engine, but this you can change -} it will be a new vehicle but not a new model