Very simple DB, and confirmation of which app to use

bertles86 · #1 (**permalink**) 01-06-11, 11:23

Hi folks,

I'm reasonably versed in creating DB's in anything from Access to Oracle but would just like a second opinion...

I'm creating a new db for my employers to store all data relating to our clients and the projects/jobs that we are undertaking:

1 Client can have N projects
1 Project can only have 1 Client

So keeping it really simple (for possible future expansion) I have two tables:

Code:

Clients Table

Client Number
Second Name
First Name
Invoice Address
Mobile
Landline
E-mail
Company
Notes
Date of contact

---
Projects Table

Project Number
Site Address
Site Owner
Client Number (FK)
Client First Name
Client Second Name
Notes
Status (In progress/completed etc)
Date Started
Date Finished

Are my two tables normalised as best as possible?
I'm thinking WAMP or LAMP are the best tools to do this, am I right?
For a front-end, I can just output/query with strings through a browser, but can I host this on a private network and it will function like it does on a website?
Have I missed anything as I'm very rusty?

P.S. I really want to avoid the use of Access/Excel as (a) Excel 2010 is a shocker and full of bugs and (b) this database is going to be pretty darn large and I'd prefer a more malleable application.

Thanks for any tips or help at all!

bertles86 · #2 (**permalink**) 01-07-11, 04:58

Hiya folks, any tips at all would be great.

dav1mo · #3 (**permalink**) 01-07-11, 09:43

Why carry the client's name in projects table? You already have the FK on client number. Also, it would allow for the name to be put in differently than it was put into the client table.
I don't know what kind of projects you are working on, but are you sure you wouldn't want a project for multiple clients? For instance if your projects are computer processes, you could easily have a client with the same requirements for a project that you already did for another client, then you could use a lot of the same project info and not have to reinvent the wheel on each project. The same re-use can be applied to other types of projects.
Dave

bertles86 · #4 (**permalink**) 01-07-11, 09:54

Thanks for the reply!

Each project would definitely be unique to the client, no two projects are ever the same they are always custom/bespoke. So on that front no, but thanks for asking.

As for duplicating the Client Name in the Project table, should that be removed you think...I see your point but when viewing a project it would be useful to know exactly which client we are dealing with, even if it is data redundancy.

dav1mo · #5 (**permalink**) 01-07-11, 13:25

then join to the clients table to get the name.

Internet Services

Web Development

Digital Marketing

Consumer Tech