Sql Project Database

[jcc5018]

Hey guys, I am still attempting to figure out a good solution for building a dynamic flash display of my personal autoCAD work for an online portfolio. Since I have a little PHP and SQL experience, I am thinking that maybe I could create a database with SQL and using PHPmyadmin to access it. I have some questions regarding the database design though. The structure so far is as followsProject_type: Type, Type_shortcut, CommentProject: ID,Type, Title, Project_type, program_used, Size, Features, Skills_learned, work_left, commentPicture: Project_ID, Title, Caption, LocationThe questions I have are:1. In project_type, is it really needed to have a Type such as Residential and then a type_shortcut =R. The plan is to make Project.Type connect to the type_shortcut. and then the Project.Project_type is just a more detailed explanation of the kind of residential project it may be, such as single family home or apartment for exp. 2. On each project, there would be multiple entries for SIZE, Features, Skills_learned, and work_left. When i call this up, I really want the data to be displayed as a list. Is there a way to insert rich text formatting into a database. I know you can in ACCESS but im not sure bout mysql. Or would I be able to insert it as HTML list code so that when PHP calls it up, it will just call up the HTML code and display it as a list. Actually to take that a step further, I think my end goal is to call the data to form an XML file that ActionScript would be able to read. 3. When calling the data to form into an XML structure, how do i write the query so that the pictures are listed under the right project, and the project under the right Project_type. I found a tutorial showing how to create the XML from a database, but It only showed one table. I'm a bit lost on how to get the rest. 4. When I set the location of the pictures, will it work if i use the shortened version of the path name, assuming that the php and flash files are stored in the Top level folder. Exp. (Files stored here)\gallery\3d_design\deck\Deck0.jpgFor reference, this is the sample XML format that I think my data is going end up in:

<Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\xampp\htdocs\jonsonlineportfolio\projects\xml\XSDFile1.xsd">	<Project_type type="Residential" comment="insert comment here">		<Project_Name name="The Getaway Retreat">			<Type>Single Family Home</Type>			<Size>Size information (list format text)</Size>			<Features>(list format text)</Features>			<Year_Designed>string</Year_Designed>			<Program_Used>string</Program_Used>			<Skills_Learned>(list format text)</Skills_Learned>			<Work_Left>(list format text)</Work_Left>			<caption>string</caption>			<Picture>				<source>image</source>				<Title>string</Title>				<caption>string</caption>			</Picture>			<Picture>				<source>image</source>				<Title>string</Title>				<caption>string</caption>			</Picture>		</Project_Name>	</Project_type></Project>

Once I figure out the correct database structure, and figure out how to convert it to XML with PHP, then hopefully i'll be able to figure out ActionScript to actually make my flash gallery. Just a note, the reason I am choosing to do this database step instead of just going to XML was because I found that trying to insert data in between all those tags and with all the potential copy and pasting and deleting I would have to do, it just seemed it would be difficult if I had to go in and make a change or add a project to a section. I felt I needed some sort of form to enter the data into and the table structure seemed to be the solution. If there is a simpler way to do the same thing that would be great to know. Im trying to make my site organized and easy to go in and change if needed, but it seems everytime i try one thing i come across a ton of problems and it still doesnt get made. So any suggestions would be great.My current site with a general idea of what i am trying to accomplish is here: http://www.personal.psu.edu/jcc5018/House3.htm

[justsomeguy]

1. In project_type, is it really needed to have a Type such as Residential and then a type_shortcut =R.

Nothing's needed, it's just how you want your system to work. It makes sense to have some type of ID and then a display name. You can link things up in the database based on the ID and if the display name ever changes you don't have to update all the records that point to it, since the ID will still be the same.

Is there a way to insert rich text formatting into a database.

You can insert whatever data you want to. The only difference between the various text fields is the number of bytes you're allowed to store. The storage requirements for the different data types are listed here:http://dev.mysql.com/doc/refman/5.0/en/sto...quirements.html

3. When calling the data to form into an XML structure, how do i write the query so that the pictures are listed under the right project, and the project under the right Project_type.

Look into the SQL JOIN. Some of the data will be duplicated, like if one project has 5 pictures you'll get 5 rows that all contain the same project information with a different picture. If you want to do anything else, it's probably best to do that processing in PHP or somewhere else other than SQL.

4. When I set the location of the pictures, will it work if i use the shortened version of the path name, assuming that the php and flash files are stored in the Top level folder. Exp. (Files stored here)\gallery\3d_design\deck\Deck0.jpg

That depends how you have things set up. The simple answer is make sure the paths are valid. So, it will work if you use the shortened version as long as the shortened version is a valid path.