[SamLis]

Hello,

I need to design a db for listing/inventory of antibodies (a biological item)
In the Exel I listed some data and invalid data, I added also my work in progress.

From allexperts:
I need to design a db for inventory of biological items called antibodies.
There are a lot LIMS (laboratory Inventory Managment Software) out there, but very expensive with a lot of bells and whistles we don't need.
I posted this question on several fora, however the difficult part seems always the terminlogy/ identifying attributes.
Since this antibodies are made up of different parts, with one part you can not alter (=defined by nature)and others we can change and by doing so change the properties of the whole antibody.
Am I on the right track?

thanks

[ScottGem]

Sam,

Just wanted to acknowledge that I'm looking at this. But it is complex so it may be a day or two before I can respond with my suggestions.

I do have a few questions though. What is the purpose of this inventory. In a normal inventory one tracks products that are being sold. So I'm a little unclear on what the top level here is. What would be the equivalent of a product? Are you tracking locations or just supply? A little more background may help.

[SamLis]

Sam,

Just wanted to acknowledge that I'm looking at this. But it is complex so it may be a day or two before I can respond with my suggestions.

I do have a few questions though. What is the purpose of this inventory. In a normal inventory one tracks products that are being sold. So I'm a little unclear on what the top level here is. What would be the equivalent of a product? Are you tracking locations or just supply? A little more background may help.

Hi,

OK, no problem, a few days, even weeks is not a problem, I convinced my boss it's not that straightforward...


Thanks a lot,
Sam

[ScottGem]

Hi,

ok, no problem, a few days, even weeks is not a problem, I convinced my boss it's not that straightforward ...


thanks a lot,
Sam

I added some questions, can you respond to them.

[SamLis]

I added some questions, can you respond to them.

I included a Word file that I started from before the Exel file,

including some images.
I hope it's more clear now,

what I want is make a list of items = the antibodies, and look for what techniques they could be used?

Now I made 4 attributes in the tblNanobodies, WB Y/N; WB concentration, IP Y/N, IP conc, maybe I have to put this in another table and use subforms?
these and many issues I hope you can help me with.

It seems very complex to me and how do you make an userfriendly data entry form for THis?

Help,

thaks,
Sam

[SamLis]

Hello,

Hope you found some time to look @ the db,
I continued with it, but I have a problem :

I use many to many relationships, so I created a junction table,
to populate this I created a form with subformjunction.


How can I search/filter my main records for a value from the subform ( the other part of the many to many relation)?

any tutorial on that?

I couldn't upload the db (too big, even zipped)
I have a db now that does what I want, but being no expert , I don't know if this is according to the rules. Will it get big very fast? Chance that it fails on me after adding a couple 100 records??

thanks

[SamLis]

Hi,
The Relationship diagram of what I have so far.

[ScottGem]

Hi,
the Relationship diagram of what I have so far.

Sorry, I have been really busy with some other things. The thing I'm not seeing here is what is your parent table, the top level I asked about earlier.

Another issue is your statement here:

Now I made 4 attributes in the tblNanobodies, WB Y/N; WB concentration, IP Y/N, IP conc,

Attributes should not be defined by fields in a record, but by values. The vast majority of entities can be modeled using a table like this:

RecordID (PK Autonumber)
ParentID (FK to parent record)
TypeID (FK to Type lookup table)
Value

So, using the above you would have a lookup table of types which could include; WB Y/N; WB concentration, IP Y/N, IP conc. You would then select one of those as the TypeID and fill in the value.

Using field names to describe data is not a properly normalized way of doing things.

[SamLis]

Hello,

No problem, it gave me the time to rethink my design, but I see no other solution...
Also, I'm not an expert on databases nor Access, so forgive me the inproper terminology. That's why I included the Word, Exel and this diagrams, hopefully that makes it more clear.

By Parent table you mean the "items" I want to make an inventory

This is tblNanobodies. But in my exel file with some sample data, you see that the records have many things (attributes?) in common.

So I moved them to lookup tables? But people told me it is better to do lookup @ form not on the table.

The tbl NanobodyGeneral forms the root (see my Word file), things that are set by mother nature, we can not change them.

However it's the combination of these attributes that identifies one NanobodyGeneral, hence here again lookup tables. Here however I need cascading comboboxes for EpitopeGeneral and EpitopeDetail , Library and PandS

WB, IP,. are techniques, and indeed I shouldn't keep them in my table
That's why I moved them to tblTechnique and made a junction table tbljNanobodyTechnique, since one Nanobody can have many techniques, for one technique you can use many nanobodies.
The concentration I left in this jtbl, since this is dependent on the technique and Nanobody

Again, I'm no expert, if you say : " this is completely wrong, hit the delete button..."

Better now than in a few moths when data is stored

Thanks

[ScottGem]

Ok, looking over the spreadsheet and work documents, you have antibody as an Entity. So that is your parent table. This will include data that is specific to the antibody itself.

Next you have the properties of the antibody. Each of those properties have a value so they are attributes of the Antibody. These go in the child table that I outlined above. So instead of having fields in table that identify a value, you have each record that identifies the property being valued and the value. Again this is a fairly standard way of setting up your database.

And yes you should have lookup tables to make sure data entry is standardized and controlled. You should not use lookup fields on the table level, but you use list controls on your form that select the value from the lookup table.

[SamLis]

Hello,
Sorry , but you lost me there...

Ok, looking over the spreadsheet and work documents, you have antibody as an Entity. So that is your parent table. This will include data that is specific to the antibody itself.

By specific , you mean what is unique for that antibody record? This would be only the NanobodyID?

Next you have the properties of the antibody. Each of those properties have a value so they are attributes of the Antibody. These go in the child table that I outlined above. So instead of having fields in table that identify a value, you have each record that identifies the property being valued and the value. Again this is a fairly standard way of setting up your database. .

So you mean things like "Expression Vector", "Tags" are Types, but where do you store the values?
How do you prevent any value can be filled in, I mean not all combiations are valid .
is there a sample database or something to have a look ?


thanks

[ScottGem]

Hello,
Sorry , but you lost me there ...



By specific , you mean what is unique for that antibody record? This would be only the NanobodyID?



So you mean things like "Expression Vector", "Tags" are Types, but where do you store the values?
How do you prevent any value can be filled in, I mean not all combiations are valid .
is there a sample database or something to have a look ?


thanks

Look at the table I outlined. You store the values in child records where you define what the value is related to by selecting a type instead of using a field name. This is a vertical structure which is what one should try to achieve. You have a more horizontal structure which is less normalized.