Need Urgent advice to a structured db

[gshomedesign]

I am a novice and have basic understanding of MySQL. I need help with the structuring of a database. I don't know what should be used in different tables or if one table will do. Here is what I will be using the db for. Please give me some good advice...

Storing information pertaining to house plans which would include some of the following:
-Plan Number
-square footage
-Custom, Stock, or Sales plan
-width and depth (ie:65'-4" x 74'-0&quot
-Designer's Name
-Unique Id generated for designer
-automated date of submission
-Unique Plan number generated for global searching (ie: I submit my plan numbered 1024lv2. Since it is actually the 3rd submission into the Table, it is given a Unique Plan Number of TGC-1003-GS. TGC stands for the global identifier. 1003 actually represents the 3rd entry. 1st entry would be 1001. GS is the designer's unique Identifier.

Note: I would assume that each designer would have his/her own table with information since another designer might have a plan numbered the same as one of mine.

Continued:
-Indentifying features (single car garage, two-story, 3 bedrooms, etc...)

I guess I really need help with issues like, should a single record contain a full string such as a persons areacode-prefix-4digit number...or should it be seperated into 2 fields separating the area code? This same question would apply to other areas such as a plan number. my plans numbers can be as simple as (1008) to as complex as (br1056-lv310b). Should this information be submitted using different fields in my form and such?
Also, assuming that each designer gets his own table for inputting, should these tables be organized in one db file or should they be different files linked to one Master file?
This is the structure that I need help with. Hopefully the information I just supplied will help. I have created a template page in html that I am still working on for all submissions into the db. I will soon convert to php for use with Macromedia Dreamweaver Mx. Please keep in mind that I will not be programming by code but by using the Dreamweaver editor.

http://www.thegalleriacollection.com/plansubmit.html

 

[GiffordS]

There really is no right answer to that. Designing a database is as much art as it is science, and there are a number of ways to achieve 3rd form. You really need to sit down and go through the information in the database piece by piece and ask yourself a few very important questions...

1) What ways will I be using the information now?

2) Will I want to use it in other ways in the future?

3) Which pieces of information are directly related, and which pieces are merely referrenced by another?

This will help you sort through the process. A good example of what I'm saying can be found in just the basic information you've given. Without knowing more of what you want to do w/ the info, my first instinct would be to create a table of designers and a table of designs. Each designer would have his/her own unique ID number. The designs would also have a unique ID number, but would be linked to the ID number of the particular designer. This would allow you much greater flexibility of at some point you want to allow guests to generate a list of designers and from there see all of that person's designs. This becomes a bit more complex if you have a separate table for each designer. By creating separate tables for each designer you are doing the work that the database is supposed to do for you.

Another example is with the plan number question. You ask if that info should be in one field or many. That would depend on how you want to use it. If you can envision wanting to use only part of a plan number, then it should be in separate fields. If not, the it should be one field.

 

[gshomedesign]

I appreciate your input. The only thing that bothers me about keeping all the plans in the same table is the fact that there could be repeat plan numbers which would make it difficult to keep each designer's stuff isolated from other designers. How could you grant a designer access to his table of info if all were shared. Ultimately, a master table would be used to connect all the individual tables for searching. Is that even possible with mySql?

My thoughts were this:
A table that contained all designers info when they sign up. This would be for my access only as I am the admin of the site.
Another table that contained all orders.
And a table for each designer's plans.
Maybe I can find a cgi script that would allow access for each designer to his info only from within the designer info table. That way, they cannot snoop the other designer's stuff.
Where can I find a good, possibly free, admin type script for my purpose? I was pretty impressed with EdatCat's cgi shopping cart program for $500. But, in order to integrate it to my needs, they wanted an additional $2,100. That is probably worth it, but, I cannot afford such fees cause I have yet to turn a profit this year. So, cheap or free is a good thing for me.

 

[OldProgrammer]

You might want to consider the following - if you hold a user id in designer table and your designs are keyed unique on designer_id + design_id

then you can

select * from designs d, designer a
where a.designer_id=d.designer_id
and a.user_id =$USER_ID

This would restrict the designer to his own designs ?

Rgds