5 Replies - 601 Views - Last Post: 21 September 2011 - 05:44 AM

#1 nighttrain   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 487
  • Joined: 22-September 10

Structure of database

Posted 19 September 2011 - 03:18 AM

Hello everyone,

I am developing an application that will be working with mssql database. I have a little trouble with structure of my database. I want to store plain names in my table : tb_plains (plain_id, plain_name). Each of plain can have many attributes. So i got table: tb_attributes(attribute_id, attribute_name). If it only this is no problem for me with relation later. Problem is all of attributes can have all or not all of (min,tar,max). For example it will be:
(temperature min, temperature tar, temperature max) next example is: (weight min, weight max), next example: (speed min). So like u all see there are attributes with his range... Now the next problem is when plain can have many attributes with his ranges he can put a value to it.

So what i want is merge a plain with some attributes with his ranges (dont must to all range pick for plain) and set value to it.

I prepar some structure but i think it's wrong:
tb_Plain
________
plain_id
plain_name

tb_Attributes
________
attribute_id
attribute_name

tb_TypeOfAttributes
________
typ_of_attribute_id
typ_of_attribute_name


So in tb_plain can be a data:
1 | Boeing
2 | MIG
3 | Boeing1000
....


In tb_Attributes
1 | temperature
2 | weight
3 | lenght
4 | strong
.....


In tb_TypeOfAttributes will be only this 3:
1 | min
2 | tar
3 | max



So one plain can have many attributes, and attribute can have all of 3 typofattribute (but can have only 1 or 2) and for all picked atributes + types user could put a value.

The output what i would like to have is:
ID | plain_id | attribute_id + typeofhisattribute | value


I want to view later in datagrid view something like this:

1 boeing temperature max 1000
1 boeing temperature min 200
1 boeing temperature tar 300
1 boeing weight min 100
1 boeing lenghth min 40
1 boeing lenghth max 200
..........................
..........................

Something like that

I don't know how it should look like (database) and where strone value...?
Hope all is cler..

Plz help

Is This A Good Question/Topic? 0
  • +

Replies To: Structure of database

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: Structure of database

Posted 19 September 2011 - 04:23 AM

You're implementing something like an EAV model. The're pretty easy to create and throw data into. However, they are be very hard to get data out of and can be very slow.

I don't see where your values are stored. Also, you're making things more complex than the need be.

Plane
   PlaneId(PK)
   Name(Unique Index)

PlaneAttr
   PlaneId(PK, FK)
   AttrName(PK)
   AttrValue


insert into Plane values(1, 'boeing');
insert into PlaneAttr values(1, 'temperature_max','1000');
insert into PlaneAttr values(1, 'temperature_min','200');
insert into PlaneAttr values(1, 'temperature_tar','300');
insert into PlaneAttr values(1, 'weight_min','100');
insert into PlaneAttr values(1, 'length_min','40');
insert into PlaneAttr values(1, 'length_max','200');



Unless you have a very good reason, I'd avoid this.

Instead:
Plane
   PlaneId(PK)
   Name(Unique Index)
   TempMax
   TempMin
   TempTar
   WeightMin
   WeightMax
   LengthMin
   LengthMax


Was This Post Helpful? 1
  • +
  • -

#3 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Structure of database

Posted 19 September 2011 - 05:00 AM

View Postbaavgai, on 19 September 2011 - 11:23 AM, said:

Unless you have a very good reason, I'd avoid this.

Agreed. Unless you actually need to be constantly altering these attribute "columns", or have thousands of them, you may as well hard code them into the table. If you allow them to take NULL entries you can even effectively exclude them from rows where they aren't needed.
Was This Post Helpful? 0
  • +
  • -

#4 nighttrain   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 487
  • Joined: 22-September 10

Re: Structure of database

Posted 19 September 2011 - 11:51 AM

Mayby a little diffrent?:
Plane
   PlaneId(PK)
   Plain_Name(Unique Index)

Attribute
   AttributeId(PK)
   Attribute_Name(Unique Index)

PlaneAttr
   PlaneAttribute_id(PK)
   Plain_id
   Attribute_id
   AttrValue


Or i got something diffrent idea, but i think a little basics, plz look at this:
One plain can have 1 record from table Attributes, it's great for manipulate data, but i think it's worst than above example. How u think, plz ur comments:
Posted Image

I want to say in my project will have about 150-200 attributes, mayby 20-30 plains and one plain can have many attributes.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: Structure of database

Posted 19 September 2011 - 01:11 PM

Why is the plain_table and attributes separate? Is there some need for the relation?

To be fair, I've played with EAVs a lot. I use them to gather up information from many different resources into one place. Perhaps the best example of an EAV store is an LDAP system.

The code for my eav looks like this:
CREATE TABLE eav.key_store (
	[entity_id] [uniqueidentifier] NOT NULL,
	[key_name] [varchar](250) NOT NULL,
	[key_value] [varchar](500) NOT NULL
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_key_store_1] ON eav.key_store([entity_id])
GO

CREATE NONCLUSTERED INDEX [idx_key_store_2] ON eav.[key_store] ([key_name])
GO

CREATE NONCLUSTERED INDEX [idx_key_store_3] ON eav.[key_store] ([entity_id],[key_name])
GO



That's it. You can model anything with key value pairs and an entity sig. This is how a number of NoSql databases operate. Of course, the queries get hairy. There's really no integrity. It's the wild west of data storage up to some outside force to impose order. In such a design, things like meta data tables are pretty much self delusion.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#6 nighttrain   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 487
  • Joined: 22-September 10

Re: Structure of database

Posted 21 September 2011 - 05:44 AM

I think that will be best way:
Posted Image

This post has been edited by nighttrain: 21 September 2011 - 05:45 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1