Need advise on a little project.

[Rippie]

Hi everyone,

This is my very first post, so i just want to say hi to everyone, and thanks to anyone who help me with this.

So back to the point, my manager would like me and a colleague to start on a little project, and i could really do with some advise on how to do this. Project is:

A page that shows what 12-15 technical people are doing 2 weeks ahead, so that sales people can see when there is presale/webex resource available. At first my colleage and i was thinking just to make a few databases for technical people, for vendors, Products and then a database with 14 days in it. DAY1,DAY2,DAY3 etc. Then we realised how difficult that is going to be, for not to mention the constant update.
I have since thought of something else, getting a "calendar" php script and then expand this to integrate with technical people and with vendors and products. Do not worry about the admin behind the scenes just yet, but in future i will get make a nice back-end for admin.

If there is an already script out there, dont hesitate to post it.

Example on how my manager wants it:

Vendor - Product - Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8, Day9, Day10
McAfee - Sidewinder - Available, Not, Available, etc, ect

Thanks in advance.
Rippie

[social_experiment]

Then we realised how difficult that is going to be, for not to mention the constant update.

1.If it's going to be a static schedule that doesn't change for any given amount of time, you could just do a static HTML coded page. For something more dynamic you will have to use either a flat file or database.

2. I don't think you need a database for days, just add a column inside the 'personel' table called 'day' ( or something similar | more applicable ) and add the day number on which that specific person will be doing something ( if bob will be doing floors on the 2nd day, add a '2' in that column. ), then select the records according to days and you should get all the records of people doing something on day 1 together, people doing something on day 2 together, etc. From a quick guesstimation you could probably do it using just one table in a database.

[tasairis]

Two tables:
First lists vendors and their products*.
Second has days available**. It tracks a product/vendor and a date***.
To query, you look up the products with an OUTER JOIN on the dates available in whatever date range you want.

* You could have a second table: one for vendors and one for products, but if all you have for a vendor is their name then (IMO) it's not worth the extra effort to split them off into a separate table... unless you think additional information might be added later (eg, vendor website, contact info, etc)

** Or unavailable. Whichever has the fewest records (if stuff is more often available than unavailable, store the unavailable dates).

*** A better design would be to track both available and unavailable. If you think you can go this far, do it.

[Rippie]

Two tables:
First lists vendors and their products*.
Second has days available**. It tracks a product/vendor and a date***.
To query, you look up the products with an OUTER JOIN on the dates available in whatever date range you want.

* You could have a second table: one for vendors and one for products, but if all you have for a vendor is their name then (IMO) it's not worth the extra effort to split them off into a separate table... unless you think additional information might be added later (eg, vendor website, contact info, etc)

** Or unavailable. Whichever has the fewest records (if stuff is more often available than unavailable, store the unavailable dates).

*** A better design would be to track both available and unavailable. If you think you can go this far, do it.

To have 1 table with vendors and their products was what i thought of first, but then i thought about how to make that table, because some vendors only have 3 products and others 20. i was thinking doing like this: ID - VENDOR - PROD1 - PROD2 - PROD3 - PROD4 and for vendors with only 3 products, just put a 0 in PROD4 and up to PROD20 just so i know it is blank.

the scope of days here is for the whole year, but only to show 14 days at a time, that is the reason for why i was thinking to have a calender or something built into it. so it knows that day and date.

Not sure how i should do this. would be nice to make an admin page where i can click on a name on a specific date and put im in or out the office. and then the DB's would update. without me having to put in the days manually.

[timWebUK]

That's not how you would do it surely? You would have a table containing vendor information, then a table containing product information. Then they'd would be connected via a relation.

1 to many relation between vendor and product.

[Rippie]

That's not how you would do it surely? You would have a table containing vendor information, then a table containing product information. Then they'd would be connected via a relation.

1 to many relation between vendor and product.

Hi Tim, thx for your post, would you care to explain to me a bit more ?

The way you describe it, to have a table containing vendor and one for product information. did you have something like this in mind:

VENDOR:
VENDOR_ID - Vendor
1 - McAfee

PRODUCT:
PROD_ID - VENDOR_ID - PROD_NAME
1 - 1 - Web Gate Way

I also imagine that i would need a table for technical people ? NAME_ID - NAME ? then another table that link technical people to a product ? ID - NAME_ID - PROD_ID ?

[timWebUK]

Not sure whether or not vender's have technicians, so leaving that out. Assuming a technician can provide advice on multiple products...

tblVender

-intVenderID (Primary Key)
-strVenderName

tblProduct

-intProdID (Primary Key)
-strProdName
-intVenderID (Foreign Key)

tblTechnician

-intTechID (Primary Key)
-strTechName
-intProdID (Foreign Key)


tblTechnician 1-* tblProduct
(One-to-many, one technician provides advice on many products)

tblVender 1-* tblProduct
(One-to-many, one vender provides many products)


By no means a perfect implementation, other people here might have something to say about that...

[Rippie]

tblTechnician 1-* tblProduct
(One-to-many, one technician provides advice on many products)

tblVender 1-* tblProduct
(One-to-many, one vender provides many products)

Sorry Tim, dont understand this bit, would you mind explain it ?

[papa]

You can also take a look at Sharepoint, there is a free version available. There a tons of scripts out there that could probably help you. It's not PHP though, but saves a lot of time depends on your requirements of course.

[timWebUK]

tblTechnician 1-* tblProduct
(One-to-many, one technician provides advice on many products)

tblVender 1-* tblProduct
(One-to-many, one vender provides many products)

Sorry Tim, dont understand this bit, would you mind explain it ?

When you have a relationship in a database between tables they joined by a primary key and foreign key. They also have a link that can be, 1-1, *-1, 1-*, or *-* (many to many, but this is frowned upon).

As each vender can have multiple products, you explain it by saying each vender (one vender), has many products.

[pytrin]

Why is many-to-many frowned upon?
A very common and useful relationship.

[timWebUK]

If there is a many-many relationship you're missing an entity, it doesn't make sense.

It would be better to have many - one<new entity>one - many.

This article can explain it better than me:

http://www.tomjewett.com/dbdesign/dbdes ... nymany.php

[pytrin]

I'm not sure what you're referring to. A many-to-many relationship is implemented using a pivot table (or a junction table as the article refers to it). What part of this is frowned upon or doesn't make sense?

[aravona]

I'm not sure what you're referring to. A many-to-many relationship is implemented using a pivot table (or a junction table as the article refers to it). What part of this is frowned upon or doesn't make sense?

I never really got why but I just did as I was told. Many to Many relationships is taught at university as wrong, having one in an assignment project would cause you to be marked down, as your database would not be fully normalised. So you'd get rid of duplicates and columns not dependant on your primary key etc etc (i hate normalisation, and promptly forgot it after my exams)

normalisation

[timWebUK]

I'm not sure what you're referring to. A many-to-many relationship is implemented using a pivot table (or a junction table as the article refers to it). What part of this is frowned upon or doesn't make sense?

The pivot/junction table is the additional entity which makes the tables theoretically not many-many.