help with an online, flash version of an excel spreadsheet!?

[mrbest]

Hi there guys.

Before I start on a project I thought I'd see if you guys could give me some pointers that might make the project easier!

Basically I am going to be creating a web-based application which will allow the user to input data for certain fields, which will save to a table and will also update graphs from the data. - Basically it will be an online, flash version of an excel spreadsheet!

Have any of you done anything like this before? Perhaps there is a really easy way of doing it?

From what I gather it will need to edit an XML file and then load this into a graph. I have found a few useful tools for creating flash based graphs. It's the creating the XML data and displaying it in a table bit that i could use some pointers on!

I hope you can help me, or point me towards some useful sites or whatever...


cheers,

mrbest

 

[Pho]

Does it have to be flash? There are graphing scripts available for PHP, in which case you could make use of an SQL database which is probably a much better solution.

 

[mrbest]

Hey Pho,

Thanks for the reply.

No I guess it doesn't have to be flash, I only said flash as I've got a better knowledge of flash than other languages! I thought PHP would be an option though.

How do you go about creating an SQL database? I seem to remember trying to use SQL database once before for a comment board but ended up going down a different route. Could you direct me to any examples/tutorials of this?

Cheers,

mrbest

 

[Pho]

Thinking about it you could always use a Flash graph coupled with PHP/SQL for the actual data entry (I originally thought you wanted people to input data using Flash).

SQL can be really quite easy. If you want to develop this on your own machine first I'd recommend you install something such as XAMPP as this includes Apache (a webserver), MySQL (the database server), PHP and PHPMyAdmin (a MySQL administrator) for you automatically.

If you Google there are loads of tutorials; I don't have any recommended ones off hand though.

What data are you wanting the user to input? If you can list pretty much everything I can have a stab at a database structure for it.

 

[mrbest]

That would be amazing. Thanks again for your help mate - really appreciate it.

Well the data will be as followed:

[per week number] Number of products delivered; number of products at status 'A'; number of products at status 'B'; number of products complete; number of products incomplete - there may be additional fields but I should be able to get a picture of what the coding structure should be from this.

Also - some of the figures entered would need to provide a percentage (perhaps using formulae from previous entries) which would intern create data for the graph - but I'm guessing this would have to happen before the information is sent.

I'm sorry if this sounds vague - I'm trying to see the bigger picture before I get into the details!

I'll get XAMPP installed and get working on some tutorials!

Thanks again

mrbest

 

[Pho]

Ok, well you can do it a number of ways ways. If you know the fields are never going to change you could go for a very simple design like this:

This allows just one entry per date, which might not be what you want. The primary key icon (the yellow key) means that this date field must be unique, therefore only one entry per date is allowed.

However, if fields are likely to change / be added in the future you may be better off with something a bit more complex like this:

This splits your data into three tables. Which are:
tblAttributes - This is a list of things such as number of products at status A or B, number produced etc. The idea behind separating these from the first design is that you now only need to include attributes relevant to each production when entering data and it also allows you to add new attributes very easily. You may find that certain production entries need certain attributes but others have no need for them, so you don't need to include them.

tblProduction - This is just a list of productions. The id column is now the primary key so that you may have more than one entry for a particular date; this really depends on your system though whether you need that.

tblProducted-Data - This bridges the production table and the attributes table. So let's say we have production run with id 3 and we want to include two attributes for it, products at status A and products at status B. Let's say that in the attributes table products at status A has an ID of 1 and products at status B has an ID of 2 and we produced 10 at status A and 20 at status B. We'd insert 2 rows into this table as follows:

Production ID, Attribute ID, Value
3, 1, 10
3, 2, 20

The lines between those tables just means this:
Between tblProduction and tblProduced-Data: for every entry in tblProduction there will be 0 to n (n=any positive number) entries in tblProduced-Data (each production entry can have lots of different attributes assigned to it).

Between tblProduced-Data and tblAttributes: each attribute can be assigned to 0 to n different production runs.



There are better ways than this design though. You may want to insert another table to further group attributes by category, i.e. a production status category or a production fault category. This way when you're doing your data entry you can nicely group it so people don't see a huge list of everything.

This design also assumes that attributes only have a numerical value rather than text.


Your formulas are best created using SQL/PHP rather than having the values saved directly into the database, it's easy to get the sum of say status A and status B over a date range and then do some calculation anyway.

But without knowing the complete picture it's hard to recommend the exact structure .


Oops sorry, I didn't mean to type so much .

 

[mrbest]

Pho! Mate this is so very helpful - I can't thank you enough! This should get me started, I'll report back to you when I get the ball rolling! I hope you don't mind I may ask you more questions when I have more of an idea of the structure. I should be able to give you more of an idea of what I need too.

Thanks again for taking the time to help me - you're great!

Ash

 

[Pho]

Sure, no problem .

I recommend you go read about normal form in database design; it's pretty much what I did above where you split your data into lots of different tables which then link to themselves based on ID numbers etc. The first method of throwing everything into a single table is a complete nightmare to modify at a later date if you want to extend the system.

If only the person who designed the system I'm working on at the moment did that my life would be so much easier .