CODEWITHSUNDEEP
database-design
jay
jay

Reputation: 3

Database Design: How to represent a group of objects in a table

Let's say I want to 'tag' (for research purposes) a set of people in an institution by the type of drugs they are addicted to using MS Access.

Something like -

Name, Type*
Mary , alcoholic
Sally , alcoholic
Joe , soft drugs
John , hard drugs
Kim , moon drugs (this should be rejected, because it's not in the table below)

addiction_type, Example*
1,alcoholic, beer
2,alcoholic, vodka
3,alcoholic, rum
4,soft drugs, aspirin
5,hard drugs, meth

If I want Type* in the first table to be a foreign key in the second table, I need to create an explicit primary key. So my question is, how do I make the addiction_type in the second table usable in table 1 (I only want the types listed under addiction_type to be used as a Type field in the first table)

I am a research analyst, so know nothing about database design expect what I read on the net.

Upvotes: 0

Views: 2384

Answers (4)

Simon
Simon

Reputation: 9425

The addiction type table (or lookup table) should contain all the unique addiction types. This way the type field in the first table can 'lookup' the addiction type in the second table. This is known as a one-to-many relationship.

Strictly speaking, it is good practise to use a ID field which is a foreign key in the first table. However for your purposes in MS access it may be easier to simply create a primary key on the addiction_type (text) field in the second table (essentially enforcing this field to be unique). Since there can only be one unique entry in the second table for the addiction type, examples must be either a (text) list or separated into another table if further lookups are required.

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

The way to do this would be to create two different tables, one for persons and one for drugs and then use a third table to indicate the association (junction).

person Table
-------------

person_id    ---primary key
person_name
person_age
--other columns

Drugs Table
-----------
Drug_id     --- primary key
drug_name
drug_type
--other columns


person_drug_asc
-------------------
person_drug_asc_id primary key
person_id --foreign key from person table
drug_id    --foreign key from drug table

The reason for this requirement is that each person might be associated to more than one drug and each drug might be associated to more than one persons. So placing the foreign key in any one table will not let you model the two situations above.

To get the drug type per person, you can use the following query.

select 
  from person per,
       drugs  dru,
       person_drug_asc pda
   where per.person_id = pda.person_id
     and dru.drug_id = pda.drug_id
     and per.person_name = '<name of the person>' -- or any other way to identify the person

This will give you the list of all the types of drugs a person is addicted to.

Upvotes: 2

LHMathies
LHMathies

Reputation: 2434

You can't make a foreign key constraint that references a column with non-unique values.

Also, it's the wrong idea. You have a concept called an addiction type, and for each type a set of people and a set of examples. That means one table of addiction types where the name is the primary key (and the only column so far), and two other tables that have foreign key constraints referencing it.

You might think that the table of addiction types is superfluous because it's implicit in the list of examples, but you are heading into a world of pain if you try to make that work in a relational database system.

Upvotes: 0

ajdams
ajdams

Reputation: 2314

I would do the following:

For the first table (people and their addictions):
Key, Name, Addiction
1, Mary, Alcoholic
2, Joe, Soft Drugs
3, Jim, Hard Drugs

For the second table (the addiction lookups):
Addiction, Examples
Alcohol, (Beer, Vodka, Rum, etc)
Soft Drugs, (Ibprufen, Nyquil, etc)

I would put a primary key on the Addiction column for the second table and then you could make the Addiction field in the first table an {FK} (not necessary and I would suggest just making a surrogate, or fake numbered key, as the primary key.

Upvotes: 0

Related Questions