How to Create Multiple One to One's

How to create multiple one to one's

You are using the inheritance (also known in entity-relationship modeling as "subclass" or "category"). In general, there are 3 ways to represent it in the database:

1. "All classes in one table": Have just one table "covering" the parent and all child classes (i.e. with all parent and child columns), with a CHECK constraint to ensure the right subset of fields is non-NULL (i.e. two different children do not "mix").
2. "Concrete class per table": Have a different table for each child, but no parent table. This requires parent's relationships (in your case Inventory <- Storage) to be repeated in all children.
3. "Class per table": Having a parent table and a separate table for each child, which is what you are trying to do. This is cleanest, but can cost some performance (mostly when modifying data, not so much when querying because you can join directly from child and skip the parent).

I usually prefer the 3rd approach, but enforce both the presence and the exclusivity of a child at the application level. Enforcing both at the database level is a bit cumbersome, but can be done if the DBMS supports deferred constraints. For example:

CHECK (
    (
        (VAN_ID IS NOT NULL AND VAN_ID = STORAGE_ID)
        AND WAREHOUSE_ID IS NULL
    )
    OR (
        VAN_ID IS NULL
        AND (WAREHOUSE_ID IS NOT NULL AND WAREHOUSE_ID = STORAGE_ID)
    )
)

This will enforce both the exclusivity (due to the CHECK) and the presence (due to the combination of CHECK and FK1/FK2) of the child.

Unfortunately, MS SQL Server does not support deferred constraints, but you may be able to "hide" the whole operation behind stored procedures and forbid clients from modifying the tables directly.

---

Just the exclusivity can be enforced without deferred constraints:

The STORAGE_TYPE is a type discriminator, usually an integer to save space (in the example above, 0 and 1 are "known" to your application and interpreted accordingly).

The VAN.STORAGE_TYPE and WAREHOUSE.STORAGE_TYPE can be computed (aka. "calculated") columns to save storage and avoid the need for the CHECKs.

--- EDIT ---

Computed columns would work under SQL Server like this:

CREATE TABLE STORAGE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE tinyint NOT NULL,
    UNIQUE (STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE VAN (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(0 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

CREATE TABLE WAREHOUSE (
    STORAGE_ID int PRIMARY KEY,
    STORAGE_TYPE AS CAST(1 as tinyint) PERSISTED,
    FOREIGN KEY (STORAGE_ID, STORAGE_TYPE) REFERENCES STORAGE(STORAGE_ID, STORAGE_TYPE)
);

-- We can make a new van.
INSERT INTO STORAGE VALUES (100, 0);
INSERT INTO VAN VALUES (100);

-- But we cannot make it a warehouse too.
INSERT INTO WAREHOUSE VALUES (100);
-- Msg 547, Level 16, State 0, Line 24
-- The INSERT statement conflicted with the FOREIGN KEY constraint "FK__WAREHOUSE__695C9DA1". The conflict occurred in database "master", table "dbo.STORAGE".

Unfortunately, SQL Server requires for a computed column which is used in a foreign key to be PERSISTED. Other databases may not have this limitation (e.g. Oracle's virtual columns), which can save some storage space.

Join one table with two other ones by id

Because members and invites are not related, you need to use two separate queries and use UNION (automatically removes duplicates) or UNION ALL (keeps duplicates) to get the output you desire:

select g.id as groupid, m.name, null as inviteid from groups g
  join members m ON m.groupid = g.id
union all 
select g.id, null, i.id from groups g
  join invites i ON (i.groupid = g.id and i.status = 1);

Output:

 groupid | name  | inviteid 
---------+-------+----------
       1 | admin |         
       1 | other |         
       1 |       |        3
       1 |       |        2
(4 rows)

Without a UNION, your query implies that the tables have some sort of relationship, so the columns are joined side-by-side. Since you want to preserve the null values, implying that the tables are not related, you need to concatenate/join them vertically with UNION

Disclosure: I work for EnterpriseDB (EDB)

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

Example queries:

 -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y

Best way to link multiple items to individual ones in MySQL

Your schema is actually pretty good. Now, your fear is that you'd query the entire contents of the tables. This is very much not true, given a good index on your primary and foreign keys!

select
    u.users_name,
    i.itemid
from
    users u
    inner join items i on
        u.user_id = i.user_id
where
    u.user_id = 1234

That rowset would return very quickly, even with hundreds of thousands of rows! The reason is because the query would use an index seek to find the row in users where that user_id is. Likewise once you join it to the items table. Therefore, it's just two very quick lookups to find all of the items you're looking for, and it won't ever have to scan the tables.

SQL is absolutely optimized for this stuff. Please take advantage of it!

One big table or multiple smaller ones?

One table for all the messages. There is simply no good reason to split a single entity into multiple tables, under most circumstances (there are some specialized circumstances where it might be a good idea). Relational databases are designed to work with large tables, not with large numbers of tables.

If performance is an issue, learn about indexing and table partitions.

Here are some reasons why you don't want multiple tables:

• Maintaining tables (adding columns, defragging, adding an index) over time is a real pain.
• Constructing queries to look at all the data is a real pain.
• Partially filled data pages can use up a lot of disk and memory.
• Backup and restore is painful, compared to the alternative of using partitions.
• Security and permissions are painful.

There are few reasons for having multiple tables in one database. One reason may be to meet security requirements. Often, applications are built using their own databases with copies of the tables. This is part of the application design.

SQL how to make one query out of multiple ones

If the month is stored as a datetime field, you can use DATEDIFF to calculate the number of months between the first and the last bill. If the number of elapsed months equals the total number of bills, the bills are consecutive.

select 
 'Customer ' + custname + ' was billed for ' +
 cast(count(*) as varchar) + ' months ' + 
 case 
  when datediff(month,min(billdate),max(billdate))+1 = count(*) 
   then 'Concurrent'
  else 'Non-Concurrent'
 end
from @billing
where billed = 1
group by custname

If you store the billing month as an integer, you can just subtract instead of using DATEDIFF. Replace the WHEN row with:

when max(billdate)-min(billdate)+1 = count(*) 

But in that case I wonder how you distinguish between years.

Separate one column into multiple ones according to words or numbers

Using dplyr and tidyr::fill, we can first replace times which do not start with "cinema" to NA. Then fill the missing values and remove rows with "cinema" in times.

library(dplyr)

current %>%
  mutate(cinema = replace(times, !grepl("^cinema", times), NA)) %>%
  tidyr::fill(cinema) %>%
  filter(!grepl("^cinema", times))

#  times  cinema
#1 10:30 cinema1
#2 12:30 cinema1
#3  9:30 cinema2
#4 16:30 cinema2
#5 17:30 cinema3

data

current <- data.frame(times = c("cinema1", "10:30", "12:30", "cinema2", "9:30", 
                        "16:30", "cinema3", "17:30"), stringsAsFactors = FALSE)

One big DAO or multiple smaller ones?

I think the answer depends on ownership.

If the Parent owns the Children, and there's no possibility of creating a Child without a Parent, then it should be just ParentDao and no ChildDao at all.

If you can create a Child without a Parent, you'll need a ChildDao for its CRUD operations. In that case, you could have the ParentDao own a reference to a ChildDao and defer Child CRUD operations to it.

Is it better to implement one-to-many relationship using tables as arrays?

In general, SQL works far better with a small number of tables than it does with a design that calls for a new table for each instance of a particular entity.

In your case the entity in question is the album. Put all your albums in one table with separate id values, according to your first design.

A query of the form

SELECT Track.Name, Album.Name
  FROM Track
  JOIN Album ON Track.Album_ID = Album.ID
 WHERE Album.Name = 'Revolver' 

is astonishingly efficient. It will exploit an SQL index on the Album_ID column. If the index is present it will avoid the full table scan ("processing ones with non-matching IDs") you mention in your question.

There's another problem with your second approach: In SQL queries, the names of tables must be constant. You cannot use the value of a column as the name of a table (or column or database).

Thousands (truly) of years of programmer time have gone into making SQL queries efficient. There's almost nothing left to be gained from trying to outsmart the query optimizer software in MySQL Or other RDBMS software.

---

Related Topics