Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Shapes, table layout?

Author  Topic 

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 15:53:10
I have several shapes, rectangular, 'T' section, 'I' Sections, flat, irregular.

The X-Sections need to be create using parameters and they can be as follows :

Rectangulat Section = LxW
'T' section = L1xW1 + L2xW2
'I' Section = L1xW1 + L2xW2 + L3xW3

etc....

Should each section be represented by a table, or can they in any way be combined?

If by different tables, how do I refer to a table when you list the shapes by category in a list box? Should there be a categories table holding the shape name and a table name?

If I had to prompt the user to enter these values how would I do so? Traverse the column names?


Mike B

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-15 : 16:31:41
My universal translator broke...Spock, beam me down a new one....

Huh?

Shapes for what?



Brett

8-)
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 16:37:54
quote:

Shapes for what?


If I told you that I would have to kill you! :)

I am trying to develop a table structure for a product costing application. The shapes represent cross sections for concrete products we manufacture. If I know the cross sectional area of a product (which must be calculated) then the end user can enter a quantity in square feet, linear feet, etc.... and I can determin volume of concrete, total weight, etc... from that.

So to answer you question, the shapes are the cross section of different product types we carry.

Mike B
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-15 : 17:01:14
OK...how about


USE Northwind
GO

CREATE TABLE Shapes (ShapeName varchar(10), ShapeDescription varchar(50))
GO
INSERT INTO Shapes(ShapeName, ShapeDescription)
SELECT 'R','This is the Rectangle shape' UNION ALL
SELECT 'T','This is the T shape' UNION ALL
SELECT 'I','This is the I shape'
GO
CREATE TABLE ShapeDimensions(ShapeName varchar(10), DimensionOrder int, Length int, Width int)
GO

INSERT INTO ShapeDimensions(ShapeName, DimensionOrder, Length, Width)
SELECT 'R',1,10,10 UNION ALL
SELECT 'T',1,10,10 UNION ALL
SELECT 'T',2,5,5 UNION ALL
SELECT 'I',1,5,5 UNION ALL
SELECT 'I',2,10,10 UNION ALL
SELECT 'I',1,5,5
GO

SELECT * FROM Shapes l INNER JOIN ShapeDimensions r ON l.ShapeName = r.ShapeName
GO

DROP TABLE Shapes
DROP TABLE ShapeDimensions
GO




Brett

8-)
Go to Top of Page

Qualm
Starting Member

7 Posts

Posted - 2004-03-15 : 17:16:29
Just out of curiousity, why would you store the cross section information in a table?

For an app like this most people would have a drop-down list of Sections, display a varying set of input boxes for the end user input (square feet, linear feet) depending on what Section was desired, and then hard code formulas to calculate volume, weight, etc based on the set of inputs.

Only if for some wild reason the formulas change (you open a branch office on the Moon), then you would need to think about storing the formula components in a database.

- Qualm
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 17:24:04
quote:
Originally posted by X002548

OK...how about


USE Northwind
GO

CREATE TABLE Shapes (ShapeName varchar(10), ShapeDescription varchar(50))
GO
INSERT INTO Shapes(ShapeName, ShapeDescription)
SELECT 'R','This is the Rectangle shape' UNION ALL
SELECT 'T','This is the T shape' UNION ALL
SELECT 'I','This is the I shape'
GO
CREATE TABLE ShapeDimensions(ShapeName varchar(10), DimensionOrder int, Length int, Width int)
GO

INSERT INTO ShapeDimensions(ShapeName, DimensionOrder, Length, Width)
SELECT 'R',1,10,10 UNION ALL
SELECT 'T',1,10,10 UNION ALL
SELECT 'T',2,5,5 UNION ALL
SELECT 'I',1,5,5 UNION ALL
SELECT 'I',2,10,10 UNION ALL
SELECT 'I',1,5,5
GO

SELECT * FROM Shapes l INNER JOIN ShapeDimensions r ON l.ShapeName = r.ShapeName
GO

DROP TABLE Shapes
DROP TABLE ShapeDimensions
GO




Brett

8-)


Nifty, very Nifty. You are one smart individual....

Now, throw in a circle shape to that, as well as a user specified shape like a double T eg.: 'TT'

Mike B
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 17:31:25
quote:
Originally posted by Qualm

Just out of curiousity, why would you store the cross section information in a table?

For an app like this most people would have a drop-down list of Sections, display a varying set of input boxes for the end user input (square feet, linear feet) depending on what Section was desired, and then hard code formulas to calculate volume, weight, etc based on the set of inputs.

Only if for some wild reason the formulas change (you open a branch office on the Moon), then you would need to think about storing the formula components in a database.

- Qualm



Because I am trying to figure out a way to allow the user to create cross section templates and I need to figure out a way to store the cross sectional properties and also to prompt the user for input as required. The templates are to be stored and used at will without having to enter the width, depth, flange, etc... every time.

Isn't that good design, allow the user to enter information once? Create template -> Select Template ->Enter value -> Calculate values based on template

Example, lets say I create a column template where XSection = 24"X24" today. Tommorow, I bid a project with that very column cross section. I select the section, enter linear feet and tada.....values and dollar values generated. That would be cool.

Mike B
Go to Top of Page

Qualm
Starting Member

7 Posts

Posted - 2004-03-15 : 17:54:29
quote:
Because I am trying to figure out a way to allow the user to create cross section templates and I need to figure out a way to store the cross sectional properties


Ah, yeah that makes sense.

What would be really neat is to have some kind of online graphing paper, with little clickable boxes that users would fill in to "draw" the shape. You can then use the number of boxes in each dimension to automatically calculate the rest.

Of course, that's a ton of work

- Qualm
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 18:05:12
quote:
Of course, that's a ton of work
- Qualm



Neat idea, but I don't think they want to go that far... :)

Mike B
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-15 : 20:29:37
What you need, is a Logo rendering engine. Then you can store Logo instructions in the DB



Damian
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-15 : 20:34:47
quote:
What would be really neat is to have some kind of online graphing paper, with little clickable boxes that users would fill in to "draw" the shape.
Believe it or not, I recently worked on a job where another developer created an interface that would do almost exactly that. He used Windows GDI functions to draw simple shapes (circles, lines, etc.) and from the shapes requested could determine size, weight, volume and so on. This could then be put into the database without having to manually enter dimensions...although he allowed an option to do so, for fine-tuning sizes. I never saw the code or its full capabilities but he said it took A LOT of work; not hard, just very involved.
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-15 : 21:45:19
Is this really that strange of a scenerio? Basically, all I want to do is create a solid and expandable structure that would allow the user to create section templates. What I have so far is:


tbShapes
Shape | ShapeName | TableName
1 | Rectangular | tbRectangular
2 | Double T | tbDoubleTee
3 | Flat Panel | tbFlatPanel

tbRectangular
Entity | SectionName | X | Y

tbDoubleTee
Entity | SectionName | Width | Height | Flange | Legs

tbTSection
Entity | SectionName | X1 | Y1 | X2 | Y2


The above is one approach that I can think of but it is probably not recommended. Reason being that if you need to add a new shape, well that means a new table has to be created. Functions have to be created etc....

Any thoughts on How I can do the above differently?

Mike B
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-16 : 07:11:34
Would something along the lines of the following work? I haven't fully thought this out, but i think it might offer you some possibilities towards a solution. It probably requires a cursor (or loop structure) to calculate the area of each shape...since the number of parameters of each shape is variable and the calculations depend on information gathered from mutliple (varying) rows.


CREATE TABLE [dbo].[Shape_Parent] (
[Code] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (20) NOT NULL ,
[Parameters] [tinyint] NOT NULL ,
[Definition] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Instance] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[FKCode] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Child] (
[FKInstanceCode] [int] NOT NULL ,
[Sequence] [tinyint] NOT NULL ,
[SegmentValue] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

Code Name Parameters Definition
---- -------------------- ---------- --------------------------------------------------
1 Circle 1 2*22/7*A
2 Triangle 3 0.5*A*B
3 Oblong 2 A*B

(3 row(s) affected)

Code FKCode
----------- ------
1 1
2 1
3 1
4 2
5 2
6 2
7 2
8 3
9 3

(9 row(s) affected)

FKInstanceCode Sequence SegmentValue
-------------- -------- --------------------
1 1 10
2 1 5
3 1 7
4 1 3
4 2 4
4 3 5
5 1 6
5 2 8
5 3 10
6 1 10
6 2 10
6 3 10
7 1 5
7 2 5
7 3 5
8 1 2
8 2 2
9 1 4
9 2 8

(19 row(s) affected)





Basically you record meta-data about each type of shape...(shape-parent) and then implement each shape....and then save the measurement for each shape-implemented.

In the above...the segmentvalue for shape_instance #1...a circle equates to a radius...and the area is 2xPIxR...as per the formula in the shape_parent.
shape_instance #4 is a triangle with sides of length 3,4,5 and an area of 0.5x3x4
etc.


The data model would need to be toughened up, to always ensure a circle has 1 parameter - no more no less....and so on for each other shape....but my gut feeling is that it would be workable.

The key is being able to define the formula and to control the parameters so that they will work with the formula....some of this might not be implementable for a DB-only solution...and some of the control mechanisms may be required to be implemented in front-end code.

However, the table structures above "should" allow you to define multiple shapes....
Everybody - feel free to jump in here and progress this!



For instance, can anybody come up with code to dynamically calculate the area of each shape listed, using only the meta-data input? Is the data model good-enough to provide a solution?
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-16 : 08:14:51
quote:

However, the table structures above "should" allow you to define multiple shapes....
Everybody - feel free to jump in here and progress this!


I thought of this structure myself as well. It seems like it should work but I am not sure how to present the user with the appropriate input prompts (input boxes, whatever..) for each shape.

quote:

For instance, can anybody come up with code to dynamically calculate the area of each shape listed, using only the meta-data input? Is the data model good-enough to provide a solution?


This is where most of the problem lies. If I use the table structure I proposed, this might be solved because I know the exact order of values in the table.... The problem is, I am not convinced my structure is the right way to go because if I need to add another shape I will need to add a table manually or come up with a way to allow the user to define tables and add them to the shapes list. Even then, how do you define the formulas for the user defined shapes?

Mike B

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-16 : 08:51:14
"how to present the user with the appropriate input prompts (input boxes, whatever..) for each shape."...maybe the (amended) table structures below can help?


"Even then, how do you define the formulas for the user defined shapes?"
Let a (super)user define them...or define them via a 'new product/shape release'...in general the number of shapes will be very limited...and the growth should be quite small. However as the formula is a key part to the control of the shapes, then you may wish to 'exercise control' over it's implementation yourself....ie you centrally issue 'new shape definitions'


CREATE TABLE [dbo].[Shape_Parent] (
[Code] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (20) NOT NULL ,
[Parameters] [tinyint] NOT NULL ,
[Definition] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Basics] (
[Code] [tinyint] NOT NULL ,
[Description] [varchar] (50) NOT NULL ,
[MinValue] [decimal](18, 2) NOT NULL ,
[MaxValue] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Definition] (
[FKParentCode] [tinyint] NOT NULL ,
[Sequence] [tinyint] NOT NULL ,
[FKBasicCode] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Instance] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[FKParentCode] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shape_Child] (
[FKInstanceCode] [int] NOT NULL ,
[FKSequence] [tinyint] NOT NULL ,
[SegmentValue] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Basics] WITH NOCHECK ADD
CONSTRAINT [PK_Shape_Basics] PRIMARY KEY NONCLUSTERED
(
[Code]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Child] WITH NOCHECK ADD
CONSTRAINT [PK_Shape_Child] PRIMARY KEY NONCLUSTERED
(
[FKInstanceCode],
[FKSequence]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Definition] WITH NOCHECK ADD
CONSTRAINT [PK_Shape_Definition] PRIMARY KEY NONCLUSTERED
(
[FKParentCode],
[Sequence]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Instance] WITH NOCHECK ADD
CONSTRAINT [PK_Shape_Instance] PRIMARY KEY NONCLUSTERED
(
[Code]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Parent] WITH NOCHECK ADD
CONSTRAINT [PK_Shape_Parent] PRIMARY KEY NONCLUSTERED
(
[Code]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shape_Child] ADD
CONSTRAINT [FK_Shape_Child_Shape_Instance] FOREIGN KEY
(
[FKInstanceCode]
) REFERENCES [dbo].[Shape_Instance] (
[Code]
)
GO

ALTER TABLE [dbo].[Shape_Definition] ADD
CONSTRAINT [FK_Shape_Definition_Shape_Basics] FOREIGN KEY
(
[FKBasicCode]
) REFERENCES [dbo].[Shape_Basics] (
[Code]
),
CONSTRAINT [FK_Shape_Definition_Shape_Parent] FOREIGN KEY
(
[FKParentCode]
) REFERENCES [dbo].[Shape_Parent] (
[Code]
)
GO

ALTER TABLE [dbo].[Shape_Instance] ADD
CONSTRAINT [FK_Shape_Instance_Shape_Parent] FOREIGN KEY
(
[FKParentCode]
) REFERENCES [dbo].[Shape_Parent] (
[Code]
)
GO


select * from shape_parent
Code Name Parameters Definition
---- -------------------- ---------- --------------------------------------------------
1 Circle 1 2*22/7*A
2 Triangle 3 0.5*A*B
3 Oblong 2 A*B

(3 row(s) affected)

select * from shape_basics
Code Description MinValue MaxValue
---- -------------------------------------------------- -------------------- --------------------
1 Side .00 9999.90
2 Angle .10 359.80
3 PartialCurve .10 359.90
4 DistanceFromPoint .10 9999.90

(4 row(s) affected)

select * from shape_definition
FKParentCode Sequence FKBasicCode
------------ -------- -----------
1 1 4
2 1 1
2 2 1
2 3 1
3 1 1
3 2 1

(6 row(s) affected)

select * from shape_instance
Code FKParentCode
----------- ------------
1 1
2 1
3 1
4 2
5 2
6 2
7 2
8 3
9 3

(9 row(s) affected)

select * from shape_child
FKInstanceCode FKSequence SegmentValue
-------------- ---------- --------------------
1 1 10
2 1 5
3 1 7
4 1 3
4 2 4
4 3 5
5 1 6
5 2 8
5 3 10
6 1 10
6 2 10
6 3 10
7 1 5
7 2 5
7 3 5
8 1 2
8 2 2
9 1 4
9 2 8

(19 row(s) affected)
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-16 : 09:31:25
quote:

CREATE TABLE [dbo].[Shape_Parent] (
[Code] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [char] (20) NOT NULL ,
[Parameters] [tinyint] NOT NULL ,
[Definition] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
....
....



Well the solution I have come up with so far is much different from this...so I will have to definately review what you have come up with.

Why is my solution so different? Am I thinking down the wrong path from from a DB Desgner point of view?

My solution at this point, but not necessarily the solution to be used!

quote:
[SIZE=1][i]

tbShapes
Shape | ShapeName | TableName
1 | Rectangular | tbRectangular
2 | Double T | tbDoubleTee
3 | Flat Panel | tbFlatPanel

tbRectangular
Entity | SectionName | X | Y

tbDoubleTee
Entity | SectionName | Width | Height | Flange | Legs

tbTSection
Entity | SectionName | X1 | Y1 | X2 | Y2


Here is my solution so far. I have used the above table strucutre and included a stored proc that will return an empty recordset for the selected table:

CREATE PROCEDURE usp_GetShapePrompts

@cTableName varchar(255)

AS

EXEC('SELECT *
FROM ' + @cTableName + '
WHERE Entry IS NULL')
GO


I called this GetShapePrompts because this is called from within the application (Front End written in C++). I get all the field names from this empty recordset and set up a ListCtrl to match as the user selects different types. This ListCtrl can then take input for each property.



Why is this a good/bad solution?

Mike B
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-16 : 09:41:13
quote:
Originally posted by AndrewMurphy

"how to present the user with the appropriate input prompts (input boxes, whatever..) for each shape."...maybe the (amended) table structures below can help?



Actually after building these tables and analyzing it in the diagram, it is great. Man you people are some smart people.

Mike B
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-03-16 : 10:21:16
The code to dynamically calculate the 'cross-sectional area' still isn't there.....until that is in place, the "smart" label isn't deserved. The above may not solve your problems, but it might get you thinking in the right direction.


"Why is my solution so different? Am I thinking down the wrong path from from a DB Desgner point of view?"...I dunno....sometimes when you're drowning, it's hard to see that the shoreline is only 1 metre away!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-16 : 10:34:18
I think the best place to start is to capture every element of data required to do the business...not just a "system"

Then categorize them in to like buckets (Entities)

Then relate the buckets...

That's where you should start..

Forcing a design with out this can lead to dead ends....



Brett

8-)
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-16 : 10:34:44
quote:
Originally posted by AndrewMurphy

The code to dynamically calculate the 'cross-sectional area' still isn't there


I am not so sure this is even realistic. The only way I can possibly see doing it dynamically is to create the formula in a string format similar to what has been done in the definition field of the Shape_Parent, parsing this string and extracting the parameters from the Shape_Definition based on sequence. One thing that must be assured however is the sequence the parameters are entered are in the order of the definition.

If the string was enterd in the format (2*(22/7))# where the # would indicate it would be a variable? Now the problem is, how to determine what operator is required simply by provided */+-.

hmmm, tough stuff

BTW
quote:

until that is in place, the "smart" label isn't deserved.


You and Brett thought of the table structure, which I did not, so that would warrent the smart lable, or at least some show of appreciation for your time!

Mike B


Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-03-16 : 10:37:35
quote:
Originally posted by X002548

I think the best place to start is to capture every element of data required to do the business...not just a "system"

Then categorize them in to like buckets (Entities)

Then relate the buckets...

That's where you should start..

Forcing a design with out this can lead to dead ends....



Brett

8-)


I did that originally, that is why I thought they required different tables (a table represents an entity right?).

Mike B
Go to Top of Page
    Next Page

- Advertisement -