Starting from scratch - design basics

maxqnz

Having spent some time now playing around with exemplar databases and following the excellent tutorial provided by Drew Jensen, I have decided to try building a real one. I am in the process of designing it, and I think I've got a handle on the whole normalisation thing, but I would really value input into whether I am on the right track or not.

The company I work for is building a panel of participants for focus groups and product testing. The participants all use this particular product, which for the purposes of the focus groups and market research is divided into two main categories (under current eligibility critieria, with a couple of other categories currently ineligible). The product is supplied by our client company and three or four others.

And the database, I need to keep names, addresses, contact details for each of the participants, along with demographic information such as the brand and type of product to the use, the frequency of use, their gender, their age, and whether they have participated in our programs previously.

While the number of brands of the core product in our marketplace is likely to remain static, new entrants into the market are not impossible, and new variants of the product are very likely. It is also possible that the database may be used to record participants in other research programs for different clients and different products.

Given all of that, I had come to the conclusions that I might need the following tables:

NAME - fields including, participant ID (key), name, address, gender, frequency of use, phone number.

PRODUCT TYPE - fields including product ID (key), brand, name, participant ID (foreign key), current eligibity (Y/N)

PROJECT - fields including project ID (key), project type, participant ID (foreign key), client ID (foreign key)

CONTACT - fields including contact ID (key), participant ID (foreign key), date, type, frequency, client ID (foreign key)

CLIENTS - fields including client ID (key), client name, product category, brand, type.

I'm not looking for someone to build this for me, and already have a sketch of what relationships I will need, but if there's a problem with my table designs, now would be a good time to find out. So do the above sound like a good start, or do I need to go back to the drawing board?
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3

olaf_noehring · General User Joined: 08 Oct 2005 Posts: 22

Hi

is there any chance you could post a screenshot of your ER model? In my opinion this helps a lot.

My ideas for your db:
Table:
Name -> "gender" should be put into another table and you should have a relation to the name table only. Maybe the same applies to your frequency. "participant id" - I would name this "tablename id" - it's much easier to "read" the ER modell.

Product type -> "brand" (same as "gender"). I hate Y/N fields (just a personal opinion). I would suggest to do the same as for gender - this gives you the opportunity to save also other values than only Y/N. Did you think of history? Do you need history = what has been in a record some time ago?

Project: "project type" - same as gender. "participant id" I would think it's m:n relation between product and participant (your table "name"). This would call for a new table. "cliend ID" - has each product only 1 client? If not it's again m:n between client and project (new table)

Contact: "type" same as gender, "frequency" maybe the same as gender

Clients: "product category", "type" and "brand" - same as gender

I have found that "notes", a field where free information can be entered is always - in almost all tables a good idea.

Again - if possible - please post a screenshot of the modell. Btw: I have the same problem - would you look on that for me?

Olaf
_________________
--
http://www.team-noehring.de/
Opensource search engine for your website:
http://www.tsep.info/

maxqnz · Posted: Mon Oct 24, 2005 2:24 pm Post subject:

olaf_noehring · General User Joined: 08 Oct 2005 Posts: 22

Hi

I have been plaing with several programs (for different databases), partly free, partly open source, partly pay.

-->

http://www.fabforce.net

-->

http://www.dbvis.com/products/dbvis/

-->

http://www.casestudio.com/

-->

http://www.eclipse.org/

http://www.azzurri.jp/en/software/clay/index.jsp

Thanks for your link. I will take a look.
Maybe you should peek here too: http://www.databaseanswers.org/data_models/index.htm

Olaf
_________________
--
http://www.team-noehring.de/
Opensource search engine for your website:
http://www.tsep.info/

olaf_noehring · General User Joined: 08 Oct 2005 Posts: 22

Hi

forgot - maybe it helps using a mindmap to collect all entities:
http://freemind.sf.net (free, open source)

Olaf
_________________
--
http://www.team-noehring.de/
Opensource search engine for your website:
http://www.tsep.info/

maxqnz · Posted: Tue Oct 25, 2005 10:52 am Post subject:

maxqnz · Posted: Wed Oct 26, 2005 2:49 am Post subject:

Well, I've taken my first steps toward trying to organise my thinking in re this DB. How much of a mess is this?

http://maxqnzs.com/anfang.html
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3

makeijan · Posted: Wed Oct 26, 2005 3:09 am Post subject: Trying to learn...

... sorry I'm a newbie too Wink

.

I'd like to know why is gender at another table. A boolean field at the main contact table would be a good idea... or not?
_________________
Forzas do ar, terra, mar e lume, a vos fago esta chamada:
si e verdade que tendes mais poder que a humana xente,
eiqui e agora,facede cos espritos dos amigos que estan fora, participen con nos desta queimada.

"Conxuro da Queimada" Extracto

maxqnz · Posted: Wed Oct 26, 2005 2:07 pm Post subject:

This will be my last appeal for help here, in order to preserve at least a passing semblance of my earlier pledge not to ask someone else to write the DB for me.

In my first sketch of the entities and attributes, I have a lot of many-to-many relationships. I would like to break some of them down, but am not sure how. For example, et's say the product is milk, which comes in four categories, regular, low-fat, non-fat, calcium-enriched, and 4 brands, farmgate, meadowfresh, anchor and tararua.

For the purposes of our example, Tararua is a brand owned by a competitor of our client, but that competitor still makes milk in each of the 4 categories.

For our projects, we may need to speak with respondents who use 1 or more categories of the product and/or 1 or more brands. Each project may also be researching one or more categories and one or more brands

That's why I have started with so many M:M relationships, until I can figure out a way to simplify the structure. If somebody could supply an example of how to remove just one of those M:M relationships, I am confident that I could grok the rest. Thanks!
_________________
Noho ora mai, ka kite ano.
What Is A Pieriansipist?

OOo 2.4/XP Pro SP2 / OOo 2.3.0.1/OpenSuse 10.3