Modeling independent attributes on m:n links

#1

Consider you have two tables - for example, "user" and "url". Now there
are several attributes for each m:n relation that can be set
independently; for example, "visited", "bookmarked" and "open on
startup". Some of these are only set for few of the m:n relations,
others can be set for most of them. Most are boolean, but a few might
carry data as well.

I see two basic ways of modeling this, both with advantages and
disadvantages.

1.) Build a standard m:n link table and add the attributes as additional
columns.

- Pro: Read queries stay simple

- Con: Write operations always have to consider that the row might not
exist yet and needs to be inserted first (using triggers or "on
duplicate key" constructs)

2.) For each attribute, add a separate m:n table. For boolean
attributes, an existing row means that the attribute is set. For
non-boolean attributes, add an extra data row.

- Pro: Clean separation of attributes, update operations are straightforward

- Con: Queries require more complex queries or extra views; possibly
reduced performance because more joins are required?

Which way of modeling do you prefer? Why, and/or in which situation?

#2

Robert,

the way presented under 1) is called EAV design and is having a lot of
disadvantages - just search in this group and the sql server groups
and find a lot of discussions on this.

Number 2) is the usually accepted one. You should make sure to have
the proper primary keys - here I would use "URL" - and then you might
be able to omit a lot of joins if you just want to query the
corresponding web address.

brgds

Philipp Post

#3

Robert,

the way presented under 1) is called EAV design and is having a lot of
disadvantages - just search in this group and the sql server groups
and find a lot of discussions on this.

Number 2) is the usually accepted one. You should make sure to have
the proper primary keys - here I would use "URL" - and then you might
be able to omit a lot of joins if you just want to query the
corresponding web address.

brgds

Philipp Post

#4

Robert,

the way presented under 1) is called EAV design and is having a lot of
disadvantages - just search in this group and the sql server groups
and find a lot of discussions on this.

Number 2) is the usually accepted one. You should make sure to have
the proper primary keys - here I would use "URL" - and then you might
be able to omit a lot of joins if you just want to query the
corresponding web address.

brgds

Philipp Post

#5

On Aug 15, 7:13 am, "Robert M." <not.an.em... (AT) adress (DOT) invalid> wrote:

Quote:

Consider you have two tables - for example, "user" and "url". Now there
are several attributes for each m:n relation that can be set
independently; for example, "visited", "bookmarked" and "open on
startup". Some of these are only set for few of the m:n relations,
others can be set for most of them. Most are boolean, but a few might
carry data as well.

I see two basic ways of modeling this, both with advantages and
disadvantages.

1.) Build a standard m:n link table and add the attributes as additional
columns.

- Pro: Read queries stay simple

- Con: Write operations always have to consider that the row might not
exist yet and needs to be inserted first (using triggers or "on
duplicate key" constructs)

2.) For each attribute, add a separate m:n table. For boolean
attributes, an existing row means that the attribute is set. For
non-boolean attributes, add an extra data row.

- Pro: Clean separation of attributes, update operations are straightforward

- Con: Queries require more complex queries or extra views; possibly
reduced performance because more joins are required?

Which way of modeling do you prefer? Why, and/or in which situation?

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here>>
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

#6

On Aug 15, 7:13 am, "Robert M." <not.an.em... (AT) adress (DOT) invalid> wrote:

Quote:

Consider you have two tables - for example, "user" and "url". Now there
are several attributes for each m:n relation that can be set
independently; for example, "visited", "bookmarked" and "open on
startup". Some of these are only set for few of the m:n relations,
others can be set for most of them. Most are boolean, but a few might
carry data as well.

I see two basic ways of modeling this, both with advantages and
disadvantages.

1.) Build a standard m:n link table and add the attributes as additional
columns.

- Pro: Read queries stay simple

- Con: Write operations always have to consider that the row might not
exist yet and needs to be inserted first (using triggers or "on
duplicate key" constructs)

2.) For each attribute, add a separate m:n table. For boolean
attributes, an existing row means that the attribute is set. For
non-boolean attributes, add an extra data row.

- Pro: Clean separation of attributes, update operations are straightforward

- Con: Queries require more complex queries or extra views; possibly
reduced performance because more joins are required?

Which way of modeling do you prefer? Why, and/or in which situation?

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here>>
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

#7

On Aug 15, 7:13 am, "Robert M." <not.an.em... (AT) adress (DOT) invalid> wrote:

Quote:

Consider you have two tables - for example, "user" and "url". Now there
are several attributes for each m:n relation that can be set
independently; for example, "visited", "bookmarked" and "open on
startup". Some of these are only set for few of the m:n relations,
others can be set for most of them. Most are boolean, but a few might
carry data as well.

I see two basic ways of modeling this, both with advantages and
disadvantages.

1.) Build a standard m:n link table and add the attributes as additional
columns.

- Pro: Read queries stay simple

- Con: Write operations always have to consider that the row might not
exist yet and needs to be inserted first (using triggers or "on
duplicate key" constructs)

2.) For each attribute, add a separate m:n table. For boolean
attributes, an existing row means that the attribute is set. For
non-boolean attributes, add an extra data row.

- Pro: Clean separation of attributes, update operations are straightforward

- Con: Queries require more complex queries or extra views; possibly
reduced performance because more joins are required?

Which way of modeling do you prefer? Why, and/or in which situation?

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here>>
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

#8

--CELKO-- wrote:

Quote:

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

This is basically the EAV design Philipp Post mentioned, but without an
extra status value table, right? The "value" column is omitted here,
because there are only boolean parameters (so an existing row implicitly
means "true")?

- Robert

#9

--CELKO-- wrote:

Quote:

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

This is basically the EAV design Philipp Post mentioned, but without an
extra status value table, right? The "value" column is omitted here,
because there are only boolean parameters (so an existing row implicitly
means "true")?

- Robert

#10

--CELKO-- wrote:

Quote:

CREATE TABLE Users
(user_id CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE URLs
(url CHAR(255) NOT NULL PRIMARY KEY,
..);

CREATE TABLE <<table name here
(user_id CHAR(255) NOT NULL
REFERENCES Users,
url CHAR(255) NOT NULL
REFERENCES URLs,
url_status VARCHAR (20) NOT NULL
CHECK url_status IN ('visited', 'bookmarked' , 'open on
startup', ..),
PRIMARY KEY (user_id, url, url_status));

This is basically the EAV design Philipp Post mentioned, but without an
extra status value table, right? The "value" column is omitted here,
because there are only boolean parameters (so an existing row implicitly
means "true")?

- Robert