need help on table design in DB :)

#11

Quote:

* * *A difference in column names does not change whether the table
design is denormalised.

How you can tell if table is denormalised or not if only info you have
about it is that it does contain some columns?

Quote:

I don't want to discuss design of this table. This is table which is
existing and I can't change it.

* * *Then why are you asking about table design if there is nothing
that can be done?

I don't want to discuss design of existing table which report is based
on. Basically this can be any table. Just some random table with some
columns. Also column names are not important or the fact if existing
table is denormalised or not. I can't change this existing data
structure.

I was asking about advice how to create new tables (or tables) to
store the report criteria in easy to access way.

Best regards,
Quleczka

#12

Quote:

In general, a "smart way to store this data in the DB" implies some kind of
new table design. *In another response, you indicate that you're stuck with
the existing table design.

Which is it?

I'm stuck with tables from which data for these custom reports comes
from. I don't want to discuss if design of these tables is
denormalised or not cause only fact which matters is that there is a
table with some columns and I have to create reports based on data
from this table.

What I I want to design is smart way is a new table (or few tables) to
store custom report criteria in it.

I hope now it's clear enough

)

Quleczka

#13

On Aug 28, 3:17*pm, Quleczka <qulec... (AT) gazeta (DOT) pl> wrote:

Quote:

Hi,

I have to make custom reports solution in PHP + MySQL and I'm looking
for the best way to design part of my database. I will appreciate any
help.

I have to store option to display custom reports on selected table.
There has to be an option to easily modify report criteria at any
time.

For example let's say I have table called 'ExampleTable' in DB which
has 20 different columns. Just few column names for example 'ID',
'Name' , 'Desc', 'Date' , 'ValueA', 'ValueB'.

User on PHP site can choose that he wants to have report for
'ExampleTable' with selected columns: 'Name', 'Date' and 'ValueB'
sorted by ID asc and he wants only rows where 'ValueB'= 4 and Date
'29/07/2008'. Basically *almost any kind of criteria. *Could be also
something like : ('ValueB'=4 OR ValueB' = 6 OR 'ValueB'>30) AND
('Date' = > '29/07/2008' OR Name = 'special' ).

Let's say everything which has form 'something AND/OR something' plus
possible parenthesis but to make it simpler only one level of
parenthesis ( not something like (A OR *(B AND C) OR D) *OR E ).

Has anyone good idea how to store it in database for easy editing
later on? I can store whole WHERE clause *as text but then I have to
parse everything in PHP to let user modify it :/

For sure I can store conditions as 'Name' = 'special' in separate
table (probably as 3 separate fields - column/condition/value ) and
then just store condition in form '(1 OR 2) AND (3 OR 4) ' *with
numbers of entries instead of basic conditions *but I have no better
idea how to divide the whole query to store it more easily :/

Any suggestions?

All ideas appreciated

Quleczka

Take a look at http://en.wikipedia.org/wiki/Categor...l_forms_(logic)

#14

Quote:

What I I want to design is smart way is a new table (or few tables) to store custom report criteria in it.

Just some thoughts on this - you will need to define

- report names and descriptions
- filters (where clauses)
- grouping - do you need subtotals?
- sorting
- columns to display and their layout on paper (column width, font,
colour)
- page layout (header, footer, subtitles)
- export formats (pdf, xls, csv, xml and whatever else and some
special settings - e. g. charset)

If you wish to put that in SQL, most of them would have a separate
table (after you defined your exact requirements). However storing
these things is more or less some kind of text processing, where you
need to maintain the order of the lines. SQL basically is founded on
unordered sets. You could put a sequence into such tables to maintain
ordering but this requires some additional overhead and maintenance in
case you re-order or remove a row. A question would be if that all
needs to go into SQL tables at all or if you could store this in some
kind of settings file on your server.

I knew a company who has rolled their own web based report creator
similar to what you describe, but it took them years to become a
usable tool for their customers. However I have no details about how
this was technically implemented.

brgds

Philipp Post

#15

Quleczka <quleczka (AT) gazeta (DOT) pl> wrote:

Quote:

* * *A difference in column names does not change whether the table
design is denormalised.

How you can tell if table is denormalised or not if only info you have
about it is that it does contain some columns?

In general, you can not. With the exception of repeating columns
within a table, denormalisation is a property of multiple tables
considered together.

Quote:

I don't want to discuss design of this table. This is table which is
existing and I can't change it.

* * *Then why are you asking about table design if there is nothing
that can be done?

I don't want to discuss design of existing table which report is based
on. Basically this can be any table. Just some random table with some
columns. Also column names are not important or the fact if existing
table is denormalised or not. I can't change this existing data
structure.

I was asking about advice how to create new tables (or tables) to
store the report criteria in easy to access way.

OK. Be aware that the denormalisation may bite in some cases.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

need help on table design in DB :)

comp.databases comp.databases

Re: need help on table design in DB :) - 08-29-2009 , 07:16 PM

Re: need help on table design in DB :) - 08-29-2009 , 07:20 PM

Re: need help on table design in DB :) - 08-31-2009 , 05:04 AM

Re: need help on table design in DB :) - 08-31-2009 , 07:45 AM

Re: need help on table design in DB :) - 09-01-2009 , 04:51 PM