CODEWITHSUNDEEP
mysqlsqldatabase
Vladyslav Yefremov
Vladyslav Yefremov

Reputation: 368

How to describe an object in sql tables?

I need to describe my object in sql tables. Each object has its ID and about 30 properties (number of properties, each property has 3 more additional sub-properties. It can be hundreds of such objects, and state of each object (number of properties, property values) is updated every 10 minutes.

So, I have a structure:

_ Object
_ _ property 1
_ _ _ sub-property 1
_ _ _ sub-property 2
_ _ _ sub-property 3
_ _ property 2
_ _ _ sub-property 1
_ _ _ sub-property 2
_ _ _ sub-property 3
...
_ _ property n
_ _ _ sub-property 1
_ _ _ sub-property 2
_ _ _ sub-property 3

Which way is better?

1. Four tables with N+1 columns, where N is max. number of properties.

-- Table with properties

| object_id | property_1 | property_2 | ... | prN |

-- Table with sub-properties 1

| object_id | sub1_1 | sub1_2 | ... | sub1_N |

-- Table with sub-properties 2

| object_id | sub2_1 | sub2_2 | ... | sub2_N |

-- Table with sub-properties 3

| object_id | sub3_1 | sub3_2 | ... | sub3_N |

Many of cells can be empty, that is not good form.

2. One table (but more rows)

| object_id | property_number | sub1 | sub2 | sub3 |

Or maybe smth else?

Upvotes: 2

Views: 670

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

In order to both:

  • Accomodate the fluctuating number of properties and sub-properties w/o leaving numerous null values in a wide table
  • Avoid value repetition as per 1NF

I would use 3 tables:

object:
-------
object_id pk| ... associated unique object values

property:
---------
property_id pk | ... associated unique property values

object_property
---------------
object_property_id pk | object_id fk | property_id fk | parent_property_id fk

This will allow you to have as many properties/sub-properties as you require.

Upvotes: 1

user4413591
user4413591

Reputation:

You should attempt to normalize the information.

In the first normal form, values aren't duplicated and each field contains one value.

In second normal form, values depend only on the primary key.

In third normal form, there are no values that are determined transitively by another value. In other words, if B depends on A and C depends on B, then C transitively depends on A.

The answer to your question by the way is to use the first one.

In your case, each main property should have a separate table alongside each individual item (in another table).

Store the id as the primary key and set foreign reference constraints.

CREATE TABLE object (
oid INT(5) NOT NULL,
name VARCHAR(60) NOT NULL UNIQUE,
pid1 INT(5) NOT NULL,
…
PRIMARY KEY(oid)
);

CREATE TABLE property_table_one (
pid INT(5) NOT NULL,
name VARCHAR(60) NOT NULL,
oid INT(5) NOT NULL,
property_one INT NOT NULL,
…
PRIMARY KEY(pid),
FOREIGN KEY(oid) REFERENCES object(oid)
);

^ - A rough idea of how you might to do it, but I realize now that it is harder than at first glance; the above may or may not suit your purposes and be normalized. It's atleast 2NF

Upvotes: 3

Related Questions