March 24, 2009 at 10:33 am
I have n number of geometric shapes like triangle, square, rectangle, circle, cone, pyramid, cylinder etc.
How can I store object's information in my database like for square, I have to store only one side, but for cylinder, I have to store circle information as well as other information.
Thanks in advance.
March 24, 2009 at 11:14 am
What sort of information about the shapes do you need to store?
March 24, 2009 at 11:19 am
I want to store information about attributes of object like sides measures, for a rectangle, width and height, for a cylinder, its height, its circle info etc.
Along with this I have to store its color information also(assume that object has only one color).
March 24, 2009 at 11:31 am
Though i usually avoid EAVs this seems like a case where you could have one table to hold all the attributes.
for example
you could have one table with the shape type and shapeId,
This could link to a table that holds all the attributes for that particular shape
with ShapeId, ShapeAttribute, Value , Units
For ShapeAttribute you could hold the values of height, length, colour, radius, depth etc....
in the Value column you could hold the value of each measure, eg Length = 2 , height = 4
and in the Units column you can put the unit of measure for each value, eg. Kgs, mm , cm etc..
This table could be cleaner with a lookup table for ShapeAttribute.
This is just a quick suggestion though without knowing what the business requirements are (if any)
March 29, 2009 at 3:15 pm
I have n number of geometric shapes like triangle, square, rectangle, circle, cone, pyramid, cylinder etc.
Hi
I hate to say that but maybe the EAV design may be an option for requirements. You may consider if you create more than one description table as we call it in our company. So there could be one to store measures (as steveb already described), one to store natural numbers, one for references - if you need - one for any text information (not indexed) and so on.
I'm just getting rid of this design by redeveloping the whole system. EAV databases have the benefit that they are flexible to hold any information but they have also many issues:
* You may ran into bad performance issues because you have always to join many and many of tables
* Getting analytic information from this database is almost impossible... Sure it is possible, but also the performance is quiet bad. The only way we have to realize this is using NOLOCK hints what causes incorrect data sometimes...
* Another big problem is that you never know if your objects are valid. Due to the fact that your objects are not stored in their own tables you don't know if the stored data for each object are correct. E.g. a square may have a radius attribute or your circle may have an angle. You don't see from outside.
* Next problem, are your objects still correct. Maybe you have some more complex objects and you add a new property to them or just conclude that your pyramid gets a new property for color which it did not have before. The existing data still don't have the property so you have to INSERT the new property with any default value into the description tables for all existing objects or the front end has to handle the possibility (forever) that the required fields are not available.
You see the EAV design is very flexible, but this is the main problem of this structure.
Sorry for my subjectivity but I fought about fife years against this database design... But this is the reason why I know the issues.
If your object types are finite you should always use own tables for objects! The EAV database design is very simple to start with but you should consider the problems.
Greets
Flo
March 29, 2009 at 3:31 pm
[font="Verdana"]Flo raises some very good points.
If your list of shapes is finite and known in advance, then model them individually. You will get better performance, and it's easier to verify that the individual shape data is correct (data integrity).
If your list of shapes is unknown and flexibility is a requirement, then modelling "extended attributes" is a reasonable solution. I would also consider adding to that design some sort of encoded "rule" to ensure that the data that makes up that shape is completed correctly so you can ensure that data integrity is maintained.
Also, make sure you do not make the mistake of doing the following:
1. using a SQLVariant to encode attribute values
2. encoding all attribute values as strings
Use the correct data types! Otherwise performance pretty much goes right out the window.
Good luck. 😀
[/font]
March 29, 2009 at 3:36 pm
1. using a SQLVariant to encode attribute values
😀
2. encoding all attribute values as strings
😀 😀 ... and now the database would not be slow or maybe corrupt. Now it would be dead!
March 30, 2009 at 9:22 am
Why not use an table to hold the actual object, the table at minimum consists of a TYPE field to identify the type of object, an ID field for an identifier and and a BLOB field to hold the object. The object could be any data type identified by the TYPE field. With this simple table you can hold any type of object and does not need a myriad of columns and only one index, if the database supports it you can also index on the blob field with a UDF to search for specific objects. I have used this method for over 20 years to store graphics, documents, GIS information, state information, scripts, reports amongst many others and still have not seen a better solution.
March 30, 2009 at 1:42 pm
Here is one way. Create a "master" shape table to reference each individual shape and a separate shape table for each shape. This is done in a way so that only valid shapes may be created and the individual tables must refer to the master entry defined of it. It's not really all that difficult.
create table Shape(
ShapeID Int not null Identity( 1, 1 ),
ShapeType Char( 2 ) not null,
Origin PointType,
Diminsiona TinyInt, -- 1, 2 or 3
Other1 ...,
Other2 ...,
Constraint UQ_Shape Primary Key NonClustered(
ShapeID
),
Constraint CK_ShapeType Check )ShapeType in (
'LN', -- Line or curve
'SP', -- Spline
'PG', -- Polygon( triangle, rectangle, any n-sided shape)
'EL', -- Ellipse( any regular curved shape)
'CY', -- Cylinder
'CN', -- Cone
'CU', -- Cube
'BL' -- Amorphous blob
))
);
go
create unique clustered index UQ_Shape
on Shape(
ShapeID, -- That's right, include the identity field
ShapeType
);
go
-- Now a table and view for every type of shape
-- Lines
create table Line(
LineID int not null,
ShapeType Char( 2 ) not null,
Length Double,
...,
Constraint PK_Line Primary Key Clustered(
LineID,
ShapeType
),
Constraint CK_LineType Check (ShapeType = 'LN'), -- Force only lines in this table
Constraint FK_LineShape Foreign Key (LineID, ShapeType) -- Force it must be a defined shape
references Shape( ShapeID, ShapeType )
);
go
create view LineShape as
select s.ShapeID, s.Origin, ..., l.Length,...
from Shape s
join Line l
on s.ShapeID = l.ShapeID
and s.ShapeType = l.ShapeType
go
-- Ellipsi
create table Ellipse(
EllipseID int not null,
ShapeType Char( 2 ) not null,
Locus PointType not null, -- Must have at least one (circle)
Locus2 PointType
...,
Constraint PK_Ellipse Primary Key Clustered(
EllipseID,
ShapeType
),
Constraint CK_LineType Check (ShapeType = 'EL'), -- Force only ellipsi in this table
Constraint FK_LineShape Foreign Key (LineID, ShapeType) -- Force it must be a defined shape
references Shape( ShapeID, ShapeType )
);
create view EllipseType....
The master table provides a "shape" entity so that other tables can maintain a reference to any shape. Notice that if an entry with ID 17 in the master shape table (Shape) is defined as a "line", then there can only be an entry in the Line table with an identifier value of 17. Thus, there cannot be, say, a Square entity with the ID value of 17. There cannot be an entry in any individual shape table without a entry already in the master table and there cannot be more than one entry in a shape table referring to the same master entry. This enforces the 1:1 relationship between the master table and the multiple other tables.
This technique requires a bit of up front design and analysis. But if you make the effort (each view also requires the proper trigger), you will be able to maintain exactly the data you need for each shape -- and you will find it surprisingly robust, scalable and yet easy to maintain.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply