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.
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 + L3xW3etc....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?Brett8-) |
|
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-15 : 17:01:14
|
OK...how aboutUSE NorthwindGOCREATE TABLE Shapes (ShapeName varchar(10), ShapeDescription varchar(50))GOINSERT INTO Shapes(ShapeName, ShapeDescription)SELECT 'R','This is the Rectangle shape' UNION ALLSELECT 'T','This is the T shape' UNION ALLSELECT 'I','This is the I shape'GOCREATE TABLE ShapeDimensions(ShapeName varchar(10), DimensionOrder int, Length int, Width int)GOINSERT INTO ShapeDimensions(ShapeName, DimensionOrder, Length, Width)SELECT 'R',1,10,10 UNION ALLSELECT 'T',1,10,10 UNION ALLSELECT 'T',2,5,5 UNION ALLSELECT 'I',1,5,5 UNION ALLSELECT 'I',2,10,10 UNION ALLSELECT 'I',1,5,5GOSELECT * FROM Shapes l INNER JOIN ShapeDimensions r ON l.ShapeName = r.ShapeNameGO DROP TABLE ShapesDROP TABLE ShapeDimensionsGO Brett8-) |
|
|
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 |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-03-15 : 17:24:04
|
quote: Originally posted by X002548 OK...how aboutUSE NorthwindGOCREATE TABLE Shapes (ShapeName varchar(10), ShapeDescription varchar(50))GOINSERT INTO Shapes(ShapeName, ShapeDescription)SELECT 'R','This is the Rectangle shape' UNION ALLSELECT 'T','This is the T shape' UNION ALLSELECT 'I','This is the I shape'GOCREATE TABLE ShapeDimensions(ShapeName varchar(10), DimensionOrder int, Length int, Width int)GOINSERT INTO ShapeDimensions(ShapeName, DimensionOrder, Length, Width)SELECT 'R',1,10,10 UNION ALLSELECT 'T',1,10,10 UNION ALLSELECT 'T',2,5,5 UNION ALLSELECT 'I',1,5,5 UNION ALLSELECT 'I',2,10,10 UNION ALLSELECT 'I',1,5,5GOSELECT * FROM Shapes l INNER JOIN ShapeDimensions r ON l.ShapeName = r.ShapeNameGO DROP TABLE ShapesDROP TABLE ShapeDimensionsGO Brett8-)
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 |
|
|
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 templateExample, 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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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. |
|
|
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:tbShapesShape | ShapeName | TableName1 | Rectangular | tbRectangular2 | Double T | tbDoubleTee3 | Flat Panel | tbFlatPaneltbRectangularEntity | SectionName | X | YtbDoubleTeeEntity | SectionName | Width | Height | Flange | LegstbTSectionEntity | 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 |
|
|
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]GOCREATE TABLE [dbo].[Shape_Instance] ( [Code] [int] IDENTITY (1, 1) NOT NULL , [FKCode] [tinyint] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Shape_Child] ( [FKInstanceCode] [int] NOT NULL , [Sequence] [tinyint] NOT NULL , [SegmentValue] [decimal](18, 0) NOT NULL ) ON [PRIMARY]GOCode Name Parameters Definition ---- -------------------- ---------- -------------------------------------------------- 1 Circle 1 2*22/7*A2 Triangle 3 0.5*A*B3 Oblong 2 A*B(3 row(s) affected)Code FKCode ----------- ------ 1 12 13 14 25 26 27 28 39 3(9 row(s) affected)FKInstanceCode Sequence SegmentValue -------------- -------- -------------------- 1 1 102 1 53 1 74 1 34 2 44 3 55 1 65 2 85 3 106 1 106 2 106 3 107 1 57 2 57 3 58 1 28 2 29 1 49 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.5x3x4etc.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? |
|
|
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 |
|
|
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]GOCREATE 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]GOCREATE TABLE [dbo].[Shape_Definition] ( [FKParentCode] [tinyint] NOT NULL , [Sequence] [tinyint] NOT NULL , [FKBasicCode] [tinyint] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Shape_Instance] ( [Code] [int] IDENTITY (1, 1) NOT NULL , [FKParentCode] [tinyint] NOT NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[Shape_Child] ( [FKInstanceCode] [int] NOT NULL , [FKSequence] [tinyint] NOT NULL , [SegmentValue] [decimal](18, 0) NOT NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Shape_Basics] WITH NOCHECK ADD CONSTRAINT [PK_Shape_Basics] PRIMARY KEY NONCLUSTERED ( [Code] ) ON [PRIMARY] GOALTER TABLE [dbo].[Shape_Child] WITH NOCHECK ADD CONSTRAINT [PK_Shape_Child] PRIMARY KEY NONCLUSTERED ( [FKInstanceCode], [FKSequence] ) ON [PRIMARY] GOALTER TABLE [dbo].[Shape_Definition] WITH NOCHECK ADD CONSTRAINT [PK_Shape_Definition] PRIMARY KEY NONCLUSTERED ( [FKParentCode], [Sequence] ) ON [PRIMARY] GOALTER TABLE [dbo].[Shape_Instance] WITH NOCHECK ADD CONSTRAINT [PK_Shape_Instance] PRIMARY KEY NONCLUSTERED ( [Code] ) ON [PRIMARY] GOALTER TABLE [dbo].[Shape_Parent] WITH NOCHECK ADD CONSTRAINT [PK_Shape_Parent] PRIMARY KEY NONCLUSTERED ( [Code] ) ON [PRIMARY] GOALTER TABLE [dbo].[Shape_Child] ADD CONSTRAINT [FK_Shape_Child_Shape_Instance] FOREIGN KEY ( [FKInstanceCode] ) REFERENCES [dbo].[Shape_Instance] ( [Code] )GOALTER 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] )GOALTER TABLE [dbo].[Shape_Instance] ADD CONSTRAINT [FK_Shape_Instance_Shape_Parent] FOREIGN KEY ( [FKParentCode] ) REFERENCES [dbo].[Shape_Parent] ( [Code] )GOselect * from shape_parentCode Name Parameters Definition ---- -------------------- ---------- -------------------------------------------------- 1 Circle 1 2*22/7*A2 Triangle 3 0.5*A*B3 Oblong 2 A*B(3 row(s) affected)select * from shape_basicsCode Description MinValue MaxValue ---- -------------------------------------------------- -------------------- -------------------- 1 Side .00 9999.902 Angle .10 359.803 PartialCurve .10 359.904 DistanceFromPoint .10 9999.90(4 row(s) affected)select * from shape_definitionFKParentCode Sequence FKBasicCode ------------ -------- ----------- 1 1 42 1 12 2 12 3 13 1 13 2 1(6 row(s) affected)select * from shape_instanceCode FKParentCode ----------- ------------ 1 12 13 14 25 26 27 28 39 3(9 row(s) affected)select * from shape_childFKInstanceCode FKSequence SegmentValue -------------- ---------- -------------------- 1 1 102 1 53 1 74 1 34 2 44 3 55 1 65 2 85 3 106 1 106 2 106 3 107 1 57 2 57 3 58 1 28 2 29 1 49 2 8(19 row(s) affected) |
|
|
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]tbShapesShape | ShapeName | TableName1 | Rectangular | tbRectangular2 | Double T | tbDoubleTee3 | Flat Panel | tbFlatPaneltbRectangularEntity | SectionName | X | YtbDoubleTeeEntity | SectionName | Width | Height | Flange | LegstbTSectionEntity | 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)ASEXEC('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 |
|
|
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 |
|
|
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! |
|
|
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....Brett8-) |
|
|
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 stuffBTWquote: 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 |
|
|
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....Brett8-)
I did that originally, that is why I thought they required different tables (a table represents an entity right?).Mike B |
|
|
Next Page
|
|
|
|
|