help with pre-pro, planning 1

[DiamondLil]

Hi guys.
Not looking for help with code right now - could use help with the planning. Want to know what in your opinion is the *proper (most organized, but not most complicated)* way to do this (theoretically)...

The scope:
Need area where all of the retailers of a mall can go to sign up to participate in(& provide content for) their mall's weekly email that goes out to all the consumers that have signed up for it.

Retailer should be able to sign up for one of the available 5 slots of any of the 52 weeks of the year (ideally not for more than 1 slot in any 1 week. If it gets too complicated coding-wise to try to restrict them, I may just indicate with text on the page that the retailer should not sign up for more than 1 slot in a week for now- hey c'mon, I'm still new!).

There should be 1 additional slot that is always reserved for the main mall to put up there own content.
For every slot that that a retailer would sign up for there would be 2 possible areas of text and 2 possible images that they would be providing for the newsletter for that week:
1. feature store logo or pic;
2. feature store text;
3. coupon pic;
4. coupon text.
Obviously retailer will need to be able to upload images (or since our host has php upload disabled, 'copy' files over).

Should be as modular as possible, because if it is utilized, will be used again for different malls.

So...
Initial area lists the:
week number,
week ending date,
available slots (just names the 5 slots platinum, gold, silver, bronze, & copper AND the mall slot rather than 1,2,3,4 &5 ) &
the name of the retailer (or blank if no one has signed up yet).

If a retailer has signed up, it makes their name a link to following info:
area that shows what contact info they have submitted, allows them to update it;
shows their content or a blank area that indicates what content we need from them;
allows them to add/edit text;
contains the upload area;
also has a field that, if checked, will collect & send their email to us so that they can be made part of an email list to whom we can send a digital proof.

In order to access this area, users should have to log in . Ideally they should have unique passwords that will allow them to edit only their info. Mall user is a super user, should have access to all the retailers areas.

Questions:
Multiple tables on server or one? How would multiple tables "talk" to each other?

Should the images just be stored in a directory on the server or as part of one of the db tables where the rest of the info is?

How should I handle the user/login area? Would it be easier to assume for now that the usernames & passwords will be assigned by us first and given to the users? How much more difficult would it be to try to create area where users sign-up and choose their usernames & passwords?

Will I need to use sessions?

What should be developed first?

In trying to think this through, I did a quick mock-up(dummy area, no code):

http://www.mallcommunications.com/retail/index.html

username: retail
password: s526

Anything that you could tell me about how you would go about setting someting like this up (I don't want to start this all wrong)?

 

[Bastien]

Questions:
Multiple tables on server or one? How would multiple tables "talk" to each other?
2 tables - one for users(hold names, id passwords etc, one for email stuff, week(s), items links to images

Should the images just be stored in a directory on the server or as part of one of the db tables where the rest of the info is? - in a folder (better performance to query string path of image than blob of image in DB) could create 1 folder for each week and code to store images for that week in separate folders

How should I handle the user/login area? Would it be easier to assume for now that the usernames & passwords will be assigned by us first and given to the users? How much more difficult would it be to try to create area where users sign-up and choose their usernames & passwords? - plenty of registration scripts out there search goolge.com, let the user sign up, provide automatic password generation and allow user to change password.

Will I need to use sessions? I would, makes it easier to keep track of users, useful for security

What should be developed first? - tables, then security (log in / signup page) then page to accept the email data


hth Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[DiamondLil]

Hey Bastien - thanks for the response.

To clarify:
You think I need only 2 tables:
1.one for users that holds names, contact info, id, passwords
2. the other for one for week #'s, which retailer has signed up for what slot, text content for newsletter stuff,
items links to images (am worried I won't have a clue as to how, since I won't know the file name of the images until they are uploaded - am I going to be able to link (& display ) these images if not in the db table.)

I know that within each of the 52 weeks, there will be 6 possible slots - should I create those directories as well for the images, or should I try to code it so that they are *somehow(I'm trying to figure out 'what' to do before the 'how' to do)* dynamically created - as I think you indicated- when a user signs up for that slot that week.

arg...that means I'll have to learn about sessions now...somehow I just don't feel ready for that one yet....

I'll take a look for a registration script - it's just that sometimes trying to learn how to make someone else's script work is harder than trying to learn how to write it myself. Then again....this may be different, it's a lot more complicated than anything I have tried to do before.

 

[Bastien]

with a little more thought something like this

table1 (user details table2 password detail table3 mall
userid userid recordid
name password userid
slots used login name slot
other details week
email text1
text2
image1
image2

this allows a better separation of security data

pages needed
login
add item
see past slots
send email (admin only)

session handling in PHP is relatively simple, just read the manual and make it go...

as for the images..it really doesn't matter as the user puts the image in the folder and the DB stores the name. that name is pulled from the db and simplely added to the html content for the email via

htmlmessage.=&quot;<tr><td><img src=&quot;$picname1&quot;></td><td>$text1&quot;;

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[DiamondLil]

Bastien,
the 'slots used' field in table 1 would store multiple #s ?
Do I need that field or would I be able to pull the info from table 3?

Also in table 3 - is the 'recordid' field an auto-increment / primary key?

Luckily, the email proof is something that will be sent out from another program - an html-based email program that can send out proofs of the newsletter you are developing to whomever you chose as you are developing it. So I just need a field in table 2 or 3 with a y or n variable that I can query later to get the emails of all the people that want it....I think.

 

[DiamondLil]

ooops, sorry - one other question...
in all the tables you have the 'userid' field, but in table 2 there is a 'login name' field. I guess I thought that the login is/was the userid and what all the tables would use.

Can you explain what each would be used for, or why we have both? Is the userid the primary key? If so, how are we using the record id - is that then just an auto-increment? Am I just confusing myself, or what?

Thanks, by the way, for your help.
-L

 

[Bastien]

the 'slots used' field in table 1 would store multiple #s ?
Do I need that field or would I be able to pull the info from table 3?

- just a counter, when a new slot is filled the counter is increased by one and the table updated

Also in table 3 - is the 'recordid' field an auto-increment / primary key?

- yes

in all the tables you have the 'userid' field, but in table 2 there is a 'login name' field. I guess I thought that the login is/was the userid and what all the tables would use.
- the userid MAY be the same or an autoincrement field, i left it open for both


anytime...



Can you explain what each would be used for, or why we have both? Is the userid the primary key? If so, how are we using the record id - is that then just an auto-increment? Am I just confusing myself, or what?

- yes you are

recordid the primary key in table3, userid cannot be as it needs to be repeated 52 imes (potentially) once each week...it is the foreign key if mysql supported foreign keys





Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[DiamondLil]

Ok - I'm beginning to see a glimmer at the end of the tunnel of understanding....thanks for your help, your explanations I've also found something that's a pretty friendly read about relational databases & joins, that's helping as well.

Can i ask you how I would make that 'slots used field' a counter? (Yes, ok, at this point I'm asking code).

 

[Bastien]

intial for a registered users is '0' (zero), can be the default enry into the db of left blank...

when a user fills inthe page to request a slot in the newsletter

$sql=&quot;update slots_used set slots_used=slots_used+1 where userid=$user&quot;;

$rsult=mysql_query($sql);

$sql2=&quot;insert into table3 values('$user','$slot',$week...)&quot;
//insert into the mall table the details of the newsletter submission

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[DiamondLil]

oh SURE - it makes perfect sense only AFTER you explain it!! cool. I'll go dive into this stuff - see if I can't get myself into trouble. Thank YOU for your help!

 

[Bastien]

no prob Bastien

There are many ways to skin this cat,
but it still tastes like chicken