Database design – do I need another table

database-design

I am trying to make a database that follows a form that the company uses.
When a client walks in the members of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. That's the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name
another table called accommodation with clientid and address
and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?

Best Answer

Another option is to just add StartDate and EndDate fields to your "Employment" table. Then when a client comes in with a change in Employer you update the EndDate for his previous Employer and add a new record for the new Employer with no EndDate. You can always look up his current employment by checking which record for that ClientID has no EndDate. If you don't need start dates or end dates you could just add a field that's basically a flag to indicate whether this is present or past employment.

Related Solutions

Classifieds Database Design

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);

Database design for cancer mouse models

As I said in my previous comment, you should have two main tables.

Observation (the origin of all cases)
Clinical_case (linked to an observation - note that "case" is an SQL keyword).

It appears to me that you are overcomplicating things with so many tables. I think that any report made by a health professional should have the same status - be it a health care worker or a vet - you can keep track of this with staff_id. Have a closed field to indicate the mouse is getting well or (gulp...) being euthanized.

What database are you using to track these experiements/observations? I would recommend PostgreSQL if you haven't yet made a choice. What is your development environment?

Go with the simple system system first - remember Occam's razor - "Entities should not be multiplied beyond necessity". But, should you really need to, you can make it more complex - as Einstein said, "Everything should be made as simple as possible, but not simpler." :-). Better to start simple rather than over-engineering.

[Edit - in response to OP's question about why PostgreSQL is better than MySQL]

more datatypes (IP address...)
CHECK CONSTRAINTS MySQL has workarounds (Triggers...) but these are clumsy and error prone,
CTEs - common table expressions - also known as the WITH clause or subquery refactoring,
Analytic functions,
a richer procedural language (or should I say language(S)),
a CREATE DOMAIN command - very useful and supported by Firebird, Oracle as well as PostgreSQL. Not by MySQL, but maybe by other RDBMSs.
generally (opinion now) - more of a focus on data integrity and correctness. MySQL's fame is built on the fact that it was quick (and nasty) - just perfect for the first Internet boom (1995 - 2000). It also was easy to set up on Windows (unlike PostgreSQL until version 8 (.3?)). It was also trendy with the GPL rather than the *BSD licence.

Best Answer

Related Solutions

Classifieds Database Design

Database design for cancer mouse models

Related Question