I have an application that I am writing and the customer wants to have a say

on the DB (I know not a good Idea) but they want forms that are filled from

a DB , ok not a problem but they way I wanted to do it was like this

single table

ID	S-ID	value		Name				active	Date_active		Date_inactive	Form_name

1	null			Qestion1			1		date			date			11

2	null			question2			1		date			null			11

3	null			question3			1		date			null			11

4	null			question4			0		date			null			12

5	1	1		answer1-question1		1		date			date			11

6	1	2		answer2-question1		1		date			date			11

7	1	3		answer3-question1		1		date			date			11

8	2	1		yes - question2		1		date			null			11

9	2	0		no - Question2		1		date			null			11

10	3			null (txt box)		1		date			null			11

11				Account

12				billing

Ect...



They want this

table 1

ID	catAns	owner		category	catreg	catText	actDate	inactdate	lookedat

1	question1	account	newsletter	0		NEWSLETTER	null		null		y

2	question2	account	exporter	0		EXPORTER	null		null		null

3	question3	account	webaddress	0		WEBADDRESS	null		null		null

4	question4	billing	billing	0		BILLING	null		null		null

Ect ...



table2

ID	cgroup	Citem		ctext		code_timestamp	actdate	inactdate

1	account	1		answer1	<binary>		<null>	<null>

2	account	2		answer2	<binary>		<null>	<null>

3	account	3		answer3	<binary>		<null>	<null>

4	billing	4		answer1	<binary>		<null>	<null>



They are concered the way I want to do it that it will show a lot of Nulls,

I told them I was building a interface for them to manage run reports and

get data out of the database, so they do not have to go in to the DB

themselves, they also want me to search on "LIKE account" instaid of SELECT

name FROM table WHERE S-ID = 1" and I also dont see what breaking the table

up in to two tables will do any good, If anyone has any insite on this

please let me know, Maybe the customer is correct.

Tim,



You haven't told us anything relevant in your entire email. How about

starting with a book on database design, and then coming back to us with:

a) a list of entities and attributes

b) a Use Case



Seriously, if I asked you: Is this a good design?



ID   SID  Name   DateTime   EIDS   Formname   CSUT

 1    Null    Q2        date        67a      something    a



How would use answer? You have no idea what I'm modelling (cars, customers,

classrooms, library), and you have no idea what my system is doing (is it a

sales database, or a collectors database, or a car registration database, or

a parking system).



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Tim Morford" <tmorford@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, July 22, 2001 9:57 PM

Subject: [asp_databases] The better way





: I have an application that I am writing and the customer wants to have a

say

: on the DB (I know not a good Idea) but they want forms that are filled

from

: a DB , ok not a problem but they way I wanted to do it was like this

: single table

: ID S-ID value Name active Date_active Date_inactive Form_name

: 1 null Qestion1 1 date date 11

: 2 null question2 1 date null 11

: 3 null question3 1 date null 11

: 4 null question4 0 date null 12

: 5 1 1 answer1-question1 1 date date 11

: 6 1 2 answer2-question1 1 date date 11

: 7 1 3 answer3-question1 1 date date 11

: 8 2 1 yes - question2 1 date null 11

: 9 2 0 no - Question2 1 date null 11

: 10 3 null (txt box) 1 date null 11

: 11 Account

: 12 billing

: Ect...

:

: They want this

: table 1

: ID catAns owner category catreg catText actDate inactdate lookedat

: 1 question1 account newsletter 0 NEWSLETTER null null y

: 2 question2 account exporter 0 EXPORTER null null null

: 3 question3 account webaddress 0 WEBADDRESS null null null

: 4 question4 billing billing 0 BILLING null null null

: Ect ...

:

: table2

: ID cgroup Citem ctext code_timestamp actdate inactdate

: 1 account 1 answer1 <binary> <null> <null>

: 2 account 2 answer2 <binary> <null> <null>

: 3 account 3 answer3 <binary> <null> <null>

: 4 billing 4 answer1 <binary> <null> <null>

:

: They are concered the way I want to do it that it will show a lot of

Nulls,

: I told them I was building a interface for them to manage run reports and

: get data out of the database, so they do not have to go in to the DB

: themselves, they also want me to search on "LIKE account" instaid of

SELECT

: name FROM table WHERE S-ID = 1" and I also dont see what breaking the

table

: up in to two tables will do any good, If anyone has any insite on this

: please let me know, Maybe the customer is correct.

:

:

: ---

: * Fast, Full-Featured Microsoft® Excel Web Reports & Charts!

: A breakthrough in high performance Web application development,

SoftArtisans

: ExcelWriter 1.1 supports native Excel charting, image insertion, and

: advanced functions & formatting. One click generates presentation-quality

: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

: Excel Object. Several editions, including ExcelWriterFREE, are available.

: http://www.softartisans.com/softartisans/excelwriter.html>




$subst('Email.Unsub')

Hi all,



I'd just like to apologise to Tim. After rereading this post, I realise that

it is a bit more curt/abrupt that what I was intending. Sorry to have caused

any offence (and to forestall any angry denunciations).



However, I do stand by my original assertion - that we need more information

(in particular about the entities being modelled, and what type of use the

data is to be put to), before any one can give an informed opinion on the

desirability of the schema.



How to design databases can be found in any good database design book. An

opinion on the desirability of this particular design requires information

about the particular situation.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Ken Schaefer" <ken@a...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, July 23, 2001 12:05 PM

Subject: [asp_databases] Re: The better way





: Tim,

:

: You haven't told us anything relevant in your entire email. How about

: starting with a book on database design, and then coming back to us with:

: a) a list of entities and attributes

: b) a Use Case

:

: Seriously, if I asked you: Is this a good design?

:

: ID   SID  Name   DateTime   EIDS   Formname   CSUT

:  1    Null    Q2        date        67a      something    a

:

: How would use answer? You have no idea what I'm modelling (cars,

customers,

: classrooms, library), and you have no idea what my system is doing (is it

a

: sales database, or a collectors database, or a car registration database,

or

: a parking system).

:

: Cheers

: Ken

:

: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: ----- Original Message -----

: From: "Tim Morford" <tmorford@n...>

: To: "ASP Databases" <asp_databases@p...>

: Sent: Sunday, July 22, 2001 9:57 PM

: Subject: [asp_databases] The better way

:

:

: : I have an application that I am writing and the customer wants to have a

: say

: : on the DB (I know not a good Idea) but they want forms that are filled

: from

: : a DB , ok not a problem but they way I wanted to do it was like this

: : single table

: : ID S-ID value Name active Date_active Date_inactive Form_name

: : 1 null Qestion1 1 date date 11

: : 2 null question2 1 date null 11

: : 3 null question3 1 date null 11

: : 4 null question4 0 date null 12

: : 5 1 1 answer1-question1 1 date date 11

: : 6 1 2 answer2-question1 1 date date 11

: : 7 1 3 answer3-question1 1 date date 11

: : 8 2 1 yes - question2 1 date null 11

: : 9 2 0 no - Question2 1 date null 11

: : 10 3 null (txt box) 1 date null 11

: : 11 Account

: : 12 billing

: : Ect...

: :

: : They want this

: : table 1

: : ID catAns owner category catreg catText actDate inactdate lookedat

: : 1 question1 account newsletter 0 NEWSLETTER null null y

: : 2 question2 account exporter 0 EXPORTER null null null

: : 3 question3 account webaddress 0 WEBADDRESS null null null

: : 4 question4 billing billing 0 BILLING null null null

: : Ect ...

: :

: : table2

: : ID cgroup Citem ctext code_timestamp actdate inactdate

: : 1 account 1 answer1 <binary> <null> <null>

: : 2 account 2 answer2 <binary> <null> <null>

: : 3 account 3 answer3 <binary> <null> <null>

: : 4 billing 4 answer1 <binary> <null> <null>

: :

: : They are concered the way I want to do it that it will show a lot of

: Nulls,

: : I told them I was building a interface for them to manage run reports

and

: : get data out of the database, so they do not have to go in to the DB

: : themselves, they also want me to search on "LIKE account" instaid of

: SELECT

: : name FROM table WHERE S-ID = 1" and I also dont see what breaking the

: table

: : up in to two tables will do any good, If anyone has any insite on this

: : please let me know, Maybe the customer is correct.

: :

: :

Well I guess I am just a dumb ass then, I am glad I need to go read a book I

think I might just do that. Thanks for the insite you pompus ass!



Tim





-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: Sunday, July 22, 2001 10:05 PM

To: ASP Databases

Subject: [asp_databases] Re: The better way





Tim,



You haven't told us anything relevant in your entire email. How about

starting with a book on database design, and then coming back to us with:

a) a list of entities and attributes

b) a Use Case



Seriously, if I asked you: Is this a good design?



ID   SID  Name   DateTime   EIDS   Formname   CSUT

 1    Null    Q2        date        67a      something    a



How would use answer? You have no idea what I'm modelling (cars, customers,

classrooms, library), and you have no idea what my system is doing (is it a

sales database, or a collectors database, or a car registration database, or

a parking system).



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Tim Morford" <tmorford@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, July 22, 2001 9:57 PM

Subject: [asp_databases] The better way





: I have an application that I am writing and the customer wants to have a

say

: on the DB (I know not a good Idea) but they want forms that are filled

from

: a DB , ok not a problem but they way I wanted to do it was like this

: single table

: ID S-ID value Name active Date_active Date_inactive Form_name

: 1 null Qestion1 1 date date 11

: 2 null question2 1 date null 11

: 3 null question3 1 date null 11

: 4 null question4 0 date null 12

: 5 1 1 answer1-question1 1 date date 11

: 6 1 2 answer2-question1 1 date date 11

: 7 1 3 answer3-question1 1 date date 11

: 8 2 1 yes - question2 1 date null 11

: 9 2 0 no - Question2 1 date null 11

: 10 3 null (txt box) 1 date null 11

: 11 Account

: 12 billing

: Ect...

:

: They want this

: table 1

: ID catAns owner category catreg catText actDate inactdate lookedat

: 1 question1 account newsletter 0 NEWSLETTER null null y

: 2 question2 account exporter 0 EXPORTER null null null

: 3 question3 account webaddress 0 WEBADDRESS null null null

: 4 question4 billing billing 0 BILLING null null null

: Ect ...

:

: table2

: ID cgroup Citem ctext code_timestamp actdate inactdate

: 1 account 1 answer1 <binary> <null> <null>

: 2 account 2 answer2 <binary> <null> <null>

: 3 account 3 answer3 <binary> <null> <null>

: 4 billing 4 answer1 <binary> <null> <null>

:

: They are concered the way I want to do it that it will show a lot of

Nulls,

: I told them I was building a interface for them to manage run reports and

: get data out of the database, so they do not have to go in to the DB

: themselves, they also want me to search on "LIKE account" instaid of

SELECT

: name FROM table WHERE S-ID = 1" and I also dont see what breaking the

table

: up in to two tables will do any good, If anyone has any insite on this

: please let me know, Maybe the customer is correct.

:

:

: ---

No Tim, your not a dumb ass.  You simply are not prepared to accept

constructive,well meant criticism.  And you really should apologize for your

foul mouth and child like behavior.  Begin, by being less proud and

accepting the fact that you are probably very uneducated, hot headed, and

lazy.   You ask for help, and then throw up on the people trying to help

you.  You - should be ashamed of yourself; I know I'm ashamed of you.



-----Original Message-----

From: Tim Morford [mailto:tmorford@n...]

Sent: Sunday, July 22, 2001 7:15 PM

To: ASP Databases

Subject: [asp_databases] Re: The better way





Well I guess I am just a dumb ass then, I am glad I need to go read a book I

think I might just do that. Thanks for the insite you pompus ass!



Tim





-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: Sunday, July 22, 2001 10:05 PM

To: ASP Databases

Subject: [asp_databases] Re: The better way





Tim,



You haven't told us anything relevant in your entire email. How about

starting with a book on database design, and then coming back to us with:

a) a list of entities and attributes

b) a Use Case



Seriously, if I asked you: Is this a good design?



ID   SID  Name   DateTime   EIDS   Formname   CSUT

 1    Null    Q2        date        67a      something    a



How would use answer? You have no idea what I'm modelling (cars, customers,

classrooms, library), and you have no idea what my system is doing (is it a

sales database, or a collectors database, or a car registration database, or

a parking system).



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Tim Morford" <tmorford@n...>

To: "ASP Databases" <asp_databases@p...>

Sent: Sunday, July 22, 2001 9:57 PM

Subject: [asp_databases] The better way





: I have an application that I am writing and the customer wants to have a

say

: on the DB (I know not a good Idea) but they want forms that are filled

from

: a DB , ok not a problem but they way I wanted to do it was like this

: single table

: ID S-ID value Name active Date_active Date_inactive Form_name

: 1 null Qestion1 1 date date 11

: 2 null question2 1 date null 11

: 3 null question3 1 date null 11

: 4 null question4 0 date null 12

: 5 1 1 answer1-question1 1 date date 11

: 6 1 2 answer2-question1 1 date date 11

: 7 1 3 answer3-question1 1 date date 11

: 8 2 1 yes - question2 1 date null 11

: 9 2 0 no - Question2 1 date null 11

: 10 3 null (txt box) 1 date null 11

: 11 Account

: 12 billing

: Ect...

:

: They want this

: table 1

: ID catAns owner category catreg catText actDate inactdate lookedat

: 1 question1 account newsletter 0 NEWSLETTER null null y

: 2 question2 account exporter 0 EXPORTER null null null

: 3 question3 account webaddress 0 WEBADDRESS null null null

: 4 question4 billing billing 0 BILLING null null null

: Ect ...

:

: table2

: ID cgroup Citem ctext code_timestamp actdate inactdate

: 1 account 1 answer1 <binary> <null> <null>

: 2 account 2 answer2 <binary> <null> <null>

: 3 account 3 answer3 <binary> <null> <null>

: 4 billing 4 answer1 <binary> <null> <null>

:

: They are concered the way I want to do it that it will show a lot of

Nulls,

: I told them I was building a interface for them to manage run reports and

: get data out of the database, so they do not have to go in to the DB

: themselves, they also want me to search on "LIKE account" instaid of

SELECT

: name FROM table WHERE S-ID = 1" and I also dont see what breaking the

table

: up in to two tables will do any good, If anyone has any insite on this

: please let me know, Maybe the customer is correct.

:

:

:

Ok everyone stay calm!



The trouble is that it's not proper to say what is the best db design until

you know what it will be used for in specific terms (i.e. how will the data

be accessed and how many accesses you expect to need to support).  This is

where the 'use case' comes in handy - it allows you to see what columns are

indexed upon, and work out which of the candidate designs will fulfil the

requirement the best.



In general though, I agree with your "... customer wants to have a say on

the DB (I know not 

a good Idea)" sentiment.  This is why we have Tables and View (or Queries in

Access).  The Tables are the domain of the DB designer (you) and the views

(queries) are the interface to the application programmer (also you!).

Since the customer is under the impression he knows what he wants (nobody

ever knows what the customer wants, least of all the customer!) this is

where a bit of politics is needed.  You have to defend your territory in the

table design and do what makes the most sense to you, as long as you can see

it mapping to a solution that will make the customer happy.  The trick is

that you can't expect the customer to appreciate that this mapping is

possible so you have to make a judgement call.  It may be that you can agree

with the customer on the "Table Design" but actually be talking in terms of

Views(queries).  Most customers will be happy at the end of the day if the

solution makes their business process more efficient and it's an extra

couple of brownie points if it looks pretty much like they dreamed it.  The

downside to this is that this will encourage the customer to stink their oar

in in future projects as it will seem like a validation of their technical

opinions.



Bottom line I guess is that when they come up with specifics, like the

design of a table, try to get to why they think that's a good idea (e.g.

they are trying to avoid seeing lots of nulls) and then don't feel

constrained to their design





> -----Original Message-----

> From: Tim Morford [mailto:tmorford@n...]

> Sent: 23 July 2001 03:15

> To: ASP Databases

> Subject: [asp_databases] Re: The better way

> 

> 

> Well I guess I am just a dumb ass then, I am glad I need to 

> go read a book I

> think I might just do that. Thanks for the insite you pompus ass!

> 

> Tim

> 

> 

> -----Original Message-----

> From: Ken Schaefer [mailto:ken@a...]

> Sent: Sunday, July 22, 2001 10:05 PM

> To: ASP Databases

> Subject: [asp_databases] Re: The better way

> 

> 

> Tim,

> 

> You haven't told us anything relevant in your entire email. How about

> starting with a book on database design, and then coming back 

> to us with:

> a) a list of entities and attributes

> b) a Use Case

> 

> Seriously, if I asked you: Is this a good design?

> 

> ID   SID  Name   DateTime   EIDS   Formname   CSUT

>  1    Null    Q2        date        67a      something    a

> 

> How would use answer? You have no idea what I'm modelling 

> (cars, customers,

> classrooms, library), and you have no idea what my system is 

> doing (is it a

> sales database, or a collectors database, or a car 

> registration database, or

> a parking system).

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ----- Original Message -----

> From: "Tim Morford" <tmorford@n...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Sunday, July 22, 2001 9:57 PM

> Subject: [asp_databases] The better way

> 

> 

> : I have an application that I am writing and the customer 

> wants to have a

> say

> : on the DB (I know not a good Idea) but they want forms that 

> are filled

> from

> : a DB , ok not a problem but they way I wanted to do it was like this

> : single table

> : ID S-ID value Name active Date_active Date_inactive Form_name

> : 1 null Qestion1 1 date date 11

> : 2 null question2 1 date null 11

> : 3 null question3 1 date null 11

> : 4 null question4 0 date null 12

> : 5 1 1 answer1-question1 1 date date 11

> : 6 1 2 answer2-question1 1 date date 11

> : 7 1 3 answer3-question1 1 date date 11

> : 8 2 1 yes - question2 1 date null 11

> : 9 2 0 no - Question2 1 date null 11

> : 10 3 null (txt box) 1 date null 11

> : 11 Account

> : 12 billing

> : Ect...

> :

> : They want this

> : table 1

> : ID catAns owner category catreg catText actDate inactdate lookedat

> : 1 question1 account newsletter 0 NEWSLETTER null null y

> : 2 question2 account exporter 0 EXPORTER null null null

> : 3 question3 account webaddress 0 WEBADDRESS null null null

> : 4 question4 billing billing 0 BILLING null null null

> : Ect ...

> :

> : table2

> : ID cgroup Citem ctext code_timestamp actdate inactdate

> : 1 account 1 answer1 <binary> <null> <null>

> : 2 account 2 answer2 <binary> <null> <null>

> : 3 account 3 answer3 <binary> <null> <null>

> : 4 billing 4 answer1 <binary> <null> <null>

> :

> : They are concered the way I want to do it that it will show a lot of

> Nulls,

> : I told them I was building a interface for them to manage 

> run reports and

> : get data out of the database, so they do not have to go in to the DB

> : themselves, they also want me to search on "LIKE account" instaid of

> SELECT

> : name FROM table WHERE S-ID = 1" and I also dont see what 

> breaking the

> table

> : up in to two tables will do any good, If anyone has any 

> insite on this

> : please let me know, Maybe the customer is correct.

> :

> :

> : ---

Ok I will clarify something now, First I want to apologize to the People on

the list, I should have not called Ken a Popmus Ass. As for being uneducated

and lazy, I don't think that is the case. Well ok I do not have any collage

education (I was labeled as Leering Disabled and was told not to go), But I

have been working with computers and writing games apps ect. since 1980,

when I was 8, I recently left a small company in the states call Bell Labs I

was a CTI engineer for the ITAC. As for lazy, I do not believe I am that is

why I was asking for help on a Sunday (the day I was working on), I work on

3 to 4 projects at a time, Right now I am currently Redesigning the

www.albanysymposium.com for the international semi-conductors conference, I

am also working on a Fed government project (the Database), an object that

can read web pages to the user that does not require a plug in or

download(for accessibility), and a VoIP service through WebPages (like the

Internet Telephony gateway). No I have not had that much with Databases, I

thought that was what this list was for, I will take my questions else

where, That is not a "hot head" statement that is a "tiered over-worked

head" I found my answer on a SQL site, The question I was trying to ask

where is there an advantage to using a "SELECT name FROM table WHERE id = 1"

over "SELECT name FROM table WHERE name = LIKE name*" and is there

performance increase or decrease in breaking up the tables.





Thanks for reading

Tim Morford



-----Original Message-----

From: Steve Carter [mailto:Steve.Carter@t...]

Sent: Monday, July 23, 2001 5:12 AM

To: ASP Databases

Subject: [asp_databases] Re: The better way





Ok everyone stay calm!



The trouble is that it's not proper to say what is the best db design until

you know what it will be used for in specific terms (i.e. how will the data

be accessed and how many accesses you expect to need to support).  This is

where the 'use case' comes in handy - it allows you to see what columns are

indexed upon, and work out which of the candidate designs will fulfil the

requirement the best.



In general though, I agree with your "... customer wants to have a say on

the DB (I know not

a good Idea)" sentiment.  This is why we have Tables and View (or Queries in

Access).  The Tables are the domain of the DB designer (you) and the views

(queries) are the interface to the application programmer (also you!).

Since the customer is under the impression he knows what he wants (nobody

ever knows what the customer wants, least of all the customer!) this is

where a bit of politics is needed.  You have to defend your territory in the

table design and do what makes the most sense to you, as long as you can see

it mapping to a solution that will make the customer happy.  The trick is

that you can't expect the customer to appreciate that this mapping is

possible so you have to make a judgement call.  It may be that you can agree

with the customer on the "Table Design" but actually be talking in terms of

Views(queries).  Most customers will be happy at the end of the day if the

solution makes their business process more efficient and it's an extra

couple of brownie points if it looks pretty much like they dreamed it.  The

downside to this is that this will encourage the customer to stink their oar

in in future projects as it will seem like a validation of their technical

opinions.



Bottom line I guess is that when they come up with specifics, like the

design of a table, try to get to why they think that's a good idea (e.g.

they are trying to avoid seeing lots of nulls) and then don't feel

constrained to their design





> -----Original Message-----

> From: Tim Morford [mailto:tmorford@n...]

> Sent: 23 July 2001 03:15

> To: ASP Databases

> Subject: [asp_databases] Re: The better way

>

>

> Well I guess I am just a dumb ass then, I am glad I need to

> go read a book I

> think I might just do that. Thanks for the insite you pompus ass!

>

> Tim

>

>

> -----Original Message-----

> From: Ken Schaefer [mailto:ken@a...]

> Sent: Sunday, July 22, 2001 10:05 PM

> To: ASP Databases

> Subject: [asp_databases] Re: The better way

>

>

> Tim,

>

> You haven't told us anything relevant in your entire email. How about

> starting with a book on database design, and then coming back

> to us with:

> a) a list of entities and attributes

> b) a Use Case

>

> Seriously, if I asked you: Is this a good design?

>

> ID   SID  Name   DateTime   EIDS   Formname   CSUT

>  1    Null    Q2        date        67a      something    a

>

> How would use answer? You have no idea what I'm modelling

> (cars, customers,

> classrooms, library), and you have no idea what my system is

> doing (is it a

> sales database, or a collectors database, or a car

> registration database, or

> a parking system).

>

> Cheers

> Ken

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ----- Original Message -----

> From: "Tim Morford" <tmorford@n...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Sunday, July 22, 2001 9:57 PM

> Subject: [asp_databases] The better way

>

>

> : I have an application that I am writing and the customer

> wants to have a

> say

> : on the DB (I know not a good Idea) but they want forms that

> are filled

> from

> : a DB , ok not a problem but they way I wanted to do it was like this

> : single table

> : ID S-ID value Name active Date_active Date_inactive Form_name

> : 1 null Qestion1 1 date date 11

> : 2 null question2 1 date null 11

> : 3 null question3 1 date null 11

> : 4 null question4 0 date null 12

> : 5 1 1 answer1-question1 1 date date 11

> : 6 1 2 answer2-question1 1 date date 11

> : 7 1 3 answer3-question1 1 date date 11

> : 8 2 1 yes - question2 1 date null 11

> : 9 2 0 no - Question2 1 date null 11

> : 10 3 null (txt box) 1 date null 11

> : 11 Account

> : 12 billing

> : Ect...

> :

> : They want this

> : table 1

> : ID catAns owner category catreg catText actDate inactdate lookedat

> : 1 question1 account newsletter 0 NEWSLETTER null null y

> : 2 question2 account exporter 0 EXPORTER null null null

> : 3 question3 account webaddress 0 WEBADDRESS null null null

> : 4 question4 billing billing 0 BILLING null null null

> : Ect ...

> :

> : table2

> : ID cgroup Citem ctext code_timestamp actdate inactdate

> : 1 account 1 answer1 <binary> <null> <null>

> : 2 account 2 answer2 <binary> <null> <null>

> : 3 account 3 answer3 <binary> <null> <null>

> : 4 billing 4 answer1 <binary> <null> <null>

> :

> : They are concered the way I want to do it that it will show a lot of

> Nulls,

> : I told them I was building a interface for them to manage

> run reports and

> : get data out of the database, so they do not have to go in to the DB

> : themselves, they also want me to search on "LIKE account" instaid of

> SELECT

> : name FROM table WHERE S-ID = 1" and I also dont see what

> breaking the

> table

> : up in to two tables will do any good, If anyone has any

> insite on this

> : please let me know, Maybe the customer is correct.

> :

> :

> : ---

Hi Tim



Depends!

If you are asking for a single response

Speed

First: if id is unique content field i.e. primary key then WHERE id=1 would

perform the fastest

Second: if id is a single index then WHERE id=1 next best

Third: if id is part of a multiple index WHERE id=1 next best

Forth: WHERE name = LIKE will do a sequential search of the table and may

bring a single

or multiple response depending how tight you make the LIKE = query

Most (ALL) database software optimize searches using primary keys and/or

indexes    



Depends part 2!

Good database design goes through a process of "normalization" a concept of 

normal forms to assist in achieving the optimum structure. There are six

Forms.



First Normal Form (1NF) says that all column values in a table must be

atomic (indivisible).

1NF dictates that for every row-by-column there exists only one value. 



Second Normal Form (2NF) says that the table is in 1NF and every non-key

column 

is fully dependent on the (entire) primary key.

2NF dictates that data stored should relate to only one thing and that if

should 

be fully described by its primary key.



Third Normal Form (3NF) says that the table is in 2NF and all non-key

columns are mutually independent.

Example:-	columns Quality,UnitCost,TotalCost where TotalCost 

Quantity*UnitCost would not meet the rule

of 3NF as TotalCost is a dependent field. TotalCost would be better handled

in a query.

 

The next three forms the Boyce Codd Normal Form, Fourth and Fifth Normal

Forms get quite 

complicated and don't provide much added value over 3NF.



What all this jargon means is that each table in a "perfect" database should

no empty feilds

contain no surplus fields and relate.



Suggestion!



Get you client to write down (pencil and A3 paper) what they wont to get out

of the database and in what format, 

what they wont to put into the database and in what format AND 

then go to the people who are putting and getting for their input. 

AND then you design the database to provide the required outcomes.



My apologies for sounding/writing like a school teacher,



Regards 



Derek



-----Original Message-----

From: Tim Morford [mailto:tmorford@n...]

Sent: Monday, 23 July 2001 22:23

To: ASP Databases

Subject: [asp_databases] Re: The better way





Ok I will clarify something now, First I want to apologize to the People on

the list, I should have not called Ken a Popmus Ass. As for being uneducated

and lazy, I don't think that is the case. Well ok I do not have any collage

education (I was labeled as Leering Disabled and was told not to go), But I

have been working with computers and writing games apps ect. since 1980,

when I was 8, I recently left a small company in the states call Bell Labs I

was a CTI engineer for the ITAC. As for lazy, I do not believe I am that is

why I was asking for help on a Sunday (the day I was working on), I work on

3 to 4 projects at a time, Right now I am currently Redesigning the

www.albanysymposium.com for the international semi-conductors conference, I

am also working on a Fed government project (the Database), an object that

can read web pages to the user that does not require a plug in or

download(for accessibility), and a VoIP service through WebPages (like the

Internet Telephony gateway). No I have not had that much with Databases, I

thought that was what this list was for, I will take my questions else

where, That is not a "hot head" statement that is a "tiered over-worked

head" I found my answer on a SQL site, The question I was trying to ask

where is there an advantage to using a "SELECT name FROM table WHERE id = 1"

over "SELECT name FROM table WHERE name = LIKE name*" and is there

performance increase or decrease in breaking up the tables.





Thanks for reading

Tim Morford

Thank you for the Reply, And the class on Normalization, it was very good.

But I went on site today, And hashed over the DB with the client, they want

forms to be auto generated by login and security, Then they want to be able

to run ahoc reports on any number of tables, data in a AND OR situation.

They ended up going with my suggestion for the DB, I was doing a 3NF and was

not knowing it, It just seamed logical to me, But I do have Null's I am not

sure how to create a DB that does not have any, and example: We have start

dates and there could be End dates, So I have a variable that says this is a

field that could end at a  unknown date but in the END Date I do not have

anything if they have not up anything in yet. I do fill it with 0 but that

is still more or less a NULL, So I guess I need more information on this

one.



Thanks for your Help and Info



Tim Morford



-----Original Message-----

From: Derek Armitage [mailto:darmitage@w...]

Sent: Monday, July 23, 2001 6:31 PM

To: ASP Databases

Subject: [asp_databases] Re: The better way





Hi Tim



Depends!

If you are asking for a single response

Speed

First: if id is unique content field i.e. primary key then WHERE id=1 would

perform the fastest

Second: if id is a single index then WHERE id=1 next best

Third: if id is part of a multiple index WHERE id=1 next best

Forth: WHERE name = LIKE will do a sequential search of the table and may

bring a single

or multiple response depending how tight you make the LIKE = query

Most (ALL) database software optimize searches using primary keys and/or

indexes



Depends part 2!

Good database design goes through a process of "normalization" a concept of

normal forms to assist in achieving the optimum structure. There are six

Forms.



First Normal Form (1NF) says that all column values in a table must be

atomic (indivisible).

1NF dictates that for every row-by-column there exists only one value.



Second Normal Form (2NF) says that the table is in 1NF and every non-key

column

is fully dependent on the (entire) primary key.

2NF dictates that data stored should relate to only one thing and that if

should

be fully described by its primary key.



Third Normal Form (3NF) says that the table is in 2NF and all non-key

columns are mutually independent.

Example:-	columns Quality,UnitCost,TotalCost where TotalCost 

Quantity*UnitCost would not meet the rule

of 3NF as TotalCost is a dependent field. TotalCost would be better handled

in a query.



The next three forms the Boyce Codd Normal Form, Fourth and Fifth Normal

Forms get quite

complicated and don't provide much added value over 3NF.



What all this jargon means is that each table in a "perfect" database should

no empty feilds

contain no surplus fields and relate.



Suggestion!



Get you client to write down (pencil and A3 paper) what they wont to get out

of the database and in what format,

what they wont to put into the database and in what format AND

then go to the people who are putting and getting for their input.

AND then you design the database to provide the required outcomes.



My apologies for sounding/writing like a school teacher,



Regards



Derek

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: But I do have Null's I am not

: sure how to create a DB that does not have any,

: and example: We have start dates and there could be End dates,

: So I have a variable that says this is a field that could end

: at a  unknown date but in the END Date I do not have

: anything if they have not up anything in yet. I do fill it with 0 but that

: is still more or less a NULL, So I guess I need more information on this

: one.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



There's nothing wrong with having NULLs in your database, as long as the

fields are valid attributes of the entity. A NULL merely indicates that you

don't know what the value should be - which seems to git your situation

exactly - you don't know when the ending date will be (or even if there will

be one).



I don't think you need a separate flag field to indicate this though...

All activities that you know have an end date - insert the end date

All activities where you do not know what the end date is - leave as NULL



You can then query by looking for fields where EndDate IS NOT NULL, or

EndDate IS NULL, in your SELECT statements.



Cheers

Ken

Hello Tim,



You'll have to forgive Ken, must have been a bad day, he's usually quite

helpful. Given that I'd have to agree though that the specifications were

just a little sparse on your part.



I gather from you're description what the client and you are looking for is

a dynamic form generation utility. If that is not the case then disregard

any further comments in this reply.



If however the dynamic form component is what you want then with the single

table design you're still looking at several self joins to get all the

content for one form so from a purely "gee what were they thinking" view on

database maintenance I'd suggest the multi-table design just for clarity

sake.



If you're going to stay with the one table design I'd suggest keeping the ID

for the form as a "node" identifier so you can get all the values for a form

in a single pass, especially if you're going to use Like to view form names.



PS. Don't let the couple of bad replies sour you on the list. Everyone's

generally very helpful.



John



> -----Original Message-----

> Subject: Re: The better way

> From: "Ken Schaefer" <ken@a...>

> Date: Mon, 23 Jul 2001 12:05:16 +1000

> X-Message-Number: 1

>

> Tim,

>

> You haven't told us anything relevant in your entire email. How about

> starting with a book on database design, and then coming back

> to us with:

> a) a list of entities and attributes

> b) a Use Case

>

> Seriously, if I asked you: Is this a good design?

>

> ID   SID  Name   DateTime   EIDS   Formname   CSUT

>  1    Null    Q2        date        67a      something    a

>

> How would use answer? You have no idea what I'm modelling

> (cars, customers,

> classrooms, library), and you have no idea what my system is

> doing (is it a

> sales database, or a collectors database, or a car

> registration database, or

> a parking system).

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Tim Morford" <tmorford@n...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Sunday, July 22, 2001 9:57 PM

> Subject: [asp_databases] The better way

>

>

> : I have an application that I am writing and the customer

> wants to have a

> say

> : on the DB (I know not a good Idea) but they want forms that

> are filled

> from

> : a DB , ok not a problem but they way I wanted to do it was like this

> : single table

> : ID S-ID value Name active Date_active Date_inactive Form_name

> : 1 null Qestion1 1 date date 11

> : 2 null question2 1 date null 11

> : 3 null question3 1 date null 11

> : 4 null question4 0 date null 12

> : 5 1 1 answer1-question1 1 date date 11

> : 6 1 2 answer2-question1 1 date date 11

> : 7 1 3 answer3-question1 1 date date 11

> : 8 2 1 yes - question2 1 date null 11

> : 9 2 0 no - Question2 1 date null 11

> : 10 3 null (txt box) 1 date null 11

> : 11 Account

> : 12 billing

> : Ect...

> :

> : They want this

> : table 1

> : ID catAns owner category catreg catText actDate inactdate lookedat

> : 1 question1 account newsletter 0 NEWSLETTER null null y

> : 2 question2 account exporter 0 EXPORTER null null null

> : 3 question3 account webaddress 0 WEBADDRESS null null null

> : 4 question4 billing billing 0 BILLING null null null

> : Ect ...

> :

> : table2

> : ID cgroup Citem ctext code_timestamp actdate inactdate

> : 1 account 1 answer1 <binary> <null> <null>

> : 2 account 2 answer2 <binary> <null> <null>

> : 3 account 3 answer3 <binary> <null> <null>

> : 4 billing 4 answer1 <binary> <null> <null>

> :

> : They are concered the way I want to do it that it will show a lot of

> Nulls,

> : I told them I was building a interface for them to manage

> run reports and

> : get data out of the database, so they do not have to go in to the DB

> : themselves, they also want me to search on "LIKE account" instaid of

> SELECT

> : name FROM table WHERE S-ID = 1" and I also dont see what

> breaking the

> table

> : up in to two tables will do any good, If anyone has any

> insite on this

> : please let me know, Maybe the customer is correct.

Well everyone I would like to thank the following John Wm. Wicks and Derek

Armitage for Contacting me off the Group to help me, and Steve Carter for

attempting to mediate, and Ken Schaefer for in a round about way teaching me

to be more descriptive in my messages. Also thanks to all the people who had

to read all that.



Tim