Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

one table is related to two other tables is that a good idea?

morde last edited by
Usually in a 1 to 1 releationship two tables are related. But today I saw a database in which one table is related to two other tables, something like this:

Example data:
```
media: 
id  | item_id | type  | filename

100 | 200     | video | video200.mp4

101 | 201     | video | video201.mp4

201 | 501     | image | awesome_photograph.png

202 | 502     | image | awesome_photoshoot.png

203 | 503     | image | awesome_icon.gif
video:
id  | duration

200 | 10.026666

201 | 32.00
image:
id  | bitdepth

500 | 24

501 | 24

502 | 24

503 | 8
```
Personally, I think this is a very strange database design because of this:
- the column type is holding 'duplicate' data for example 'image' is stored 3 times. Maybe it would be better to introduce a mediaType table, but that might have been an overkill.
However, I can see one advantage:
- the table media acts like a parent to both video and image
Questions:

Is it ok to design it like this or is this completely wrong?
Reply Quote 0
1 Reply Last reply
K
kalena last edited by

It is ok. Doctrine calls this approach Inheritance Mapping.

https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/inheritance-mapping.html
Reply Quote 0
1 Reply Last reply

database design 5167

2
Posts

0
Views

Suggested Topics

B

When normalizing a relational database, should multiple "type" tables be combined into one?
SQL, Database Testing • postgresql database design normalization relational theory • • baileigh

2

0
Votes

2
Posts

0
Views

I would go for separate tables for each type. With so few rows performance will not be a consideration either way. Individual tables will only have a page or two each. Similarly for the combined table. Either way you will have to join a "type" table to complete your views. Inserts are infrequent at this level of abstraction so locking is unlikely to be an issue either way. For me, though, having different tables keeps logically distinct items separated, which is a cleaner design. There will be many different tables which will have a relationship to a type table of some sort. If there are many different type tables the foreign key relationships are self-documenting and enforcable through DRI. With one combined table it is difficult to use DRI to say "Venue.TypeID comes from Type.TypeID but only if the Type.Category = 'V'", for example. The complementary side of this relationship - "Type.TypeID with Type.Category = 'V' can only be used in the Venue table" - is also not supported in any RDBMS's DRI that I know of.
E

Best way to design this mileage table?
SQL, Database Testing • database design sql server 2008 sc languagehema • • elysha

2

0
Votes

2
Posts

0
Views

T

Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct. Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while. Capture all data constraints using CHECK constraints. Here is a pseudo-schema definition: id PRIMARY KEY , truck_num , start_date , end_date CHECK (end_date > start_date) , start_miles CHECK (start_miles >= 0) , end_miles CHECK (end_miles > start_miles) -- what if they refill the tank? , start_gals CHECK (start_gals >= 0) , end_gals CHECK (end_gals < start_gals AND end_gals >= 0) -- all these should be computed , miles = end_miles - start_miles , gals = start_gals - end_gals , mpg = miles/gals As for indexing the table, here's what I think: Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme. You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.) Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it. When you're done, test your setup as follows: Create a test table Pump it full of test data Create the indexes Update statistics Run your most common queries Check their plans and run times
G

How to set-up a distributed data storage system for The Real Junkfood Project
SQL, Database Testing • database design users database rec languageomme • • guilherme

2

0
Votes

2
Posts

0
Views

Generally speaking I would recommend a full fledged relational database. This will give you everything you need, and allow you to grow gracefully. As far as what exact engine to use that depends on personal preference/budgetary constraints/hosting requirements/compatability with the application layer/etc. Personally I would probably use the Microsoft stack (SqlServer database, C# for the application layer, etc) because that is what I'm most familiar with, and would be most efficient in coding. Others may choose other platforms based on X,Y,Z reasons. The truth is any mainstream relational database engine will support your needs. I wouldn't recommend csv files because that is highly likely to become unmanageable. I would overwhelmingly recommend Sql Lite over csv files given only these two options. Both are lightweight, but Sql Lite at least offers some database functionally. Here is a good link for what SqlLite is good for. Basically good: local storage and/or replacing CSV/custom data storage files. Not designed for: Replacing client/server SQL database engines (SqlServer, Oracle, PostgreSQL, etc). I also wouldn't recommend Google Fusion Tables because I believe it'll be harder to manage as you grow. I could be wrong but that is my general experience with those types of solutions. Also Fusion Tables is an experimental app, so I would be worried about what happens if the "experiment" fails... I wouldn't recommend NoSql because I don't think you will gain much if anything, and relational type reporting will be complicated.
S

Design: How do you decide when items are related vs when they are attributes?
SQL, Database Testing • database design erd • • shizuka

2

0
Votes

2
Posts

0
Views

R

One rule of thumb is that if it is 'physical' (concrete as opposed to abstract) then it is an entity otherwise it is an attribute. Another rule of thumb in ER modelling is that an table EITHER models an entity OR a relationship between entities but never both. There can be other kinds of table, of course e.g. it is sometimes conducive to put all attributes of the same type (e.g. colors) in a table, known as a lookup table (i.e. is neither an entity table nor a relationship table but is still valid and useful).
K

join the two tables which possess one to many relationship
SQL, Database Testing • join rdbms • • Kadyn

2

0
Votes

2
Posts

0
Views

A

The relationship appears to be between content_field_mem_id.field_mem_id_value and ex.mem_id However, since content_field_mem_id.field_mem_id_value is an int and ex.mem_id is a varchar, you will need to cast/convert one of the columns as part of your join. It would be more reliable (although slower) to convert the int column to a varchar. You didn't say which DB technology you are using, so I can't show the exact syntax for the convert (it is very different between MS SQL/Oracle/DB2/etc).
D

Should I declare one or two tables to represent Items and Sub Items?
SQL, Database Testing • mysql database design • • Duquan

2

0
Votes

2
Posts

0
Views

J

This looks like a straightforward one-to-many relationship, which as you suggested you can model by setting a foreign key in the child items table referring to the parent items table. As for performance, you won't have any kind of performance hit at all for a small site. Modelling your schema properly is your priority. Consider renaming the tables too. For example, if the shop offers a cleaning service then you could think of the tables in terms of Services (e.g. car) and Costs (seats, trunk, etc)
Store row count or use query to calculate on Primary Key
SQL, Database Testing • mysql database design normalization • • carriann

2

0
Votes

2
Posts

0
Views

C

Redundant information in a database is a no-no. If you have an index (such as the PRIMARY KEY) on user_id and if there is not thousands of rows for a user, the computing it on the fly is the way to go.
How to handle versions of an Entity in Database Design
SQL, Database Testing • database design erd version c languageontrol • • briley

2

0
Votes

2
Posts

0
Views

M

You could keep them all in the same table, simply with a column to track version numbers: documents id (part of PK) version_number (part of PK) title file creator_id (FK to users.id) version_date (the date that this version is created - not necessary, but good to have) file Your application will need to be capable of handling updating the version number of a document when a new record is added but it is a new version of an existing document. You also need to ensure that any document metadata (such as Title) changes are propagated to all related documents. You could do this by allowing document ID to not be unique, and the primary key is a combination of document ID and version number. Otherwise, it could be impossible to tell which record to update. Another way would be to have a stub for document metadata and a separate table for all versions: documents id (PK) title creator (FK to users.id) (other metadata) document_versions id (PK) document_id (FK to documents.id) document_version_number file (other fields) The second option is a slightly more complicated structure, but will give you better referential integrity because all version must be linked to a document stub by a foreign key. Personally, I'd go with the second version.
Y

Row for each date, or 31 columns?
SQL, Database Testing • mysql database design normalization • • yajahira

2

0
Votes

2
Posts

0
Views

A

It depends on what you are going to do with the data, as it always does. It's usually dangerous to assume that the data is never going to be used in some other way. Your question illustrates this nicely. The original design of "summaries" assumed that weekly summarization would always be the intended use, and now you want daily summarization. The structure of the summaries table (or view) is what can be called crosstabulation. Crosstabulation is frequently used in spreadsheets as a convenient way of displaying data that has been summarized along two dimensions. See the pivot table feature for more details. Crosstabulated data is not in first normal form. The folks who invented first normal form were thinking about something when they invented it. They were thinking about keyed access to all data. The power of keyed access to all data is central to the simplicity and power of the relational data model. You can look up more detail under first normal form. Based on this, I would say that one row per line item is the best structure for this data, assuming that no finer granularity can be obtained. If you want to summarize by day, or by week, or by month or whatever, you can summarize by using a pivot operation in a view. This will take some computer resources, to be sure. Computer resources are usually cheap, compared to "we can't do what you want because the data isn't organized that way". As far as existing code goes, just create views that make the data look the way it needs to look for the existing code, and tweak the existing code to use the view. Unless the functionality of the existing code needs to be changed as well. Edit based on comments: You probably know more about cross tabulation than you think you do. Look at the diagram you provided at the top of your question. That's a cross tabulation. It tabulates by product down the left side, and by week number across the top. The cells contain some kind of aggregate statistic, like the total count of bath sheets sold in week 2. It's probably a summary of invoice data, or something like that.
D

Access IFf Query
SQL, Database Testing • database design update • • Duquan

2

0
Votes

2
Posts

0
Views

I don't know much of T-SQL but I believe that to compare strings you need the logical operator AND. Try this: UPDATE StaffCopy SET StaffCopy.LastName=IIF(([LastName]='Avon' AND [FirstName]='Sarah'), 'Tyne', [LastName]);
E

store two tables into a single table in mySQL
SQL, Database Testing • mysql • • esther

2

0
Votes

2
Posts

0
Views

A

If you want to stream changes from tempA and tempA1 to temp, you can use trigger to do this create trigger for both tables Example: DELIMITER $$ CREATE TRIGGER `insert_to_temp` AFTER INSERT ON `tempa` FOR EACH ROW BEGIN if new.txt1 is not null THEN insert into temp values(new.txt1); END IF; END$$ DELIMITER ; And do this for tempa1 but if you want to do a batch insert you can run: insert into temp select * from tempa where txt1 is not null union select * from tempa1 where txt2 is not null
R

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?
SQL, Database Testing • sql server performance query performance sql server 2016 merge data sync languagehroniza • • Rossere

2

0
Votes

2
Posts

0
Views

R

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table? I think, in general, I'd say probably not. But it depends on a lot of factors. Bulk Loading (minimal logging) SELECT...INTO will benefit from bulk load optimizations. The INSERT portion of the MERGE query might benefit from these optimizations, but the UPDATE portion won't. So the MERGE statement will likely result in more transaction log writes, making it slower. DROP vs DELETE SELECT...INTO will also benefit from the fact that DROP TABLE is a metadata operation, and thus will "remove" all the rows very quickly. The DELETE portion of the MERGE query will be logged normally, resulting in more transaction log writes, making it slower. Concurrency SELECT...INTO will benefit from reduced locking / blocking / concurrency issues, because it will have exclusive access to the target table. The MERGE statement will potentially have to deal with lock escalation, blocking by other processes, etc, unless you provide TABLOCK hints. Index Maintenance The MERGE statement also has to deal with index maintenance (you mentioned there is a clustered index and a nonclustered index on the target table in this scenario), whereas the SELECT...INTO does not. To mitigate this, you could potentially drop the indexes and recreate them after the MERGE has run. Additional thoughts All of this somewhat depends on what the breakdown (in terms of # of rows) is between INSERTs, UPDATEs, and DELETEs in the MERGE statement, but in general I would expect the SELECT...INTO case to be quicker unless the MERGE statement has been designed very carefully. Some related information that might be interesting to you and this situation: Why "Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable The Data Loading Performance Guide Sidebar: in a comment, you mentioned: I'm asking because in my case I have to synchronize about 300 million records, and with my testing of only 5 million records, the SELECT INTO took about 30 seconds, the MERGE took about 4 minutes. To compare "apples to apples" make sure to include the time it takes to create the two indexes you mentioned for the SELECT...INTO case: In my specific case, there's actually no indexes on TableB at the time of the SELECT INTO, but a single unique clustered (PK) and a single non-clustered index is created on it on after the data is inserted. If a MERGE query was used instead, then both tables would have unique clustered indexes and non-clustered indexes on them.
T

Review of Log Messages Database Schema
SQL, Database Testing • database design • • Trenton

2

0
Votes

2
Posts

0
Views

S

First, I love that you have isolated your actual messages into it's own table, that was going to be my first suggestion but you had already gotten there. Otherwise, I think your first design is good. By keeping the message log itself (the meta data) isolated from the message body you will (with proper indexes) be able to find messages very quickly. My only word of advice for you would be to do the delete check as part of the transaction that is writing the message (and to make that a small number). This will make the cleanup action part of the message writing logic and keep things moving along quickly. Something like: Delete TOP (100) old messages that satisfy criteria Insert new message
D

Using a many-to-many table with a one-to-many relation?
SQL, Database Testing • mysql database design • • Duquan

2

0
Votes

2
Posts

0
Views

Your customers may not offer every possible attribute, right? E.g., "T-Shirt, Cotton" is available in small, medium, and large, but my store only offers small and medium. Maybe even black shirts in small and white shirts in small and medium, but not black shirts in medium. To support that, you need a table which normalizes attributes (one record per possible value, and you need to join your Shops table to that. So suppliers would populate these tables: CREATE TABLE Products ( ProductID INT NOT NULL PRIMARY KEY, SupplierID INT NOT NULL, CategoryID INT, Description VARCHAR(100) NOT NULL, BasePrice DECIMAL(18, 2) NOT NULL, Image VARBINARY(MAX), UNIQUE (SupplierID, Description) ) CREATE TABLE ProductAttributes ( AttributeID INT NOT NULL PRIMARY KEY, ProductID INT NOT NULL, Dimension VARCHAR(100) NOT NULL, Value VARCHAR(100) NOT NULL, PriceModifier DECIMAL(18, 2) NOT NULL DEFAULT 0.00, Image VARBINARY(MAX), UNIQUE (ProductID, Dimension, Value) ) Shops would then populate two tables to show what they offer, one for products and one for each intersection of attributes. It'll be important that the UI allow store owners to quickly add all attributes; you don't want them ticking a box for every record. CREATE TABLE ProductsOffered ( ShopID INT NOT NULL, ProductID INT NOT NULL, PRIMARY KEY (ShopID, ProductID), Description VARCHAR(100) NOT NULL, Image VARBINARY(MAX), BasePrice DECIMAL(18, 2) NOT NULL ) CREATE TABLE AttributesBuckets ( ShopID INT NOT NULL, BucketID INT NOT NULL, AttributeID INT NOT NULL, -- Implies ProductID PRIMARY KEY (ShopID, BucketID, AttributeID), Image VARBINARY(MAX), PriceModifier DECIMAL(18, 2) NOT NULL DEFAULT 0.00, ) So if a store decides to sell T-shirts in "black" and "small" there will be a record in AttributesBuckets for ShopID = MyShop, ProductID = T-Shirt, BucketID = 1, AttributeID = color/black, and another record with AttributeID = size/small. That's one "bucket;" one combination of attributes which fully describes a product that can be sold. If they also sell small white shirts, that's one more bucket, defined by two more records. If you add a "stitching" option, each bucket will have three records, for color, size, and stitching. As a bonus, you can now track inventory: CREATE TABLE Inventory ( ShopID INT NOT NULL, BucketID INT NOT NULL, PRIMARY KEY (ShopID, BucketID), Quantity INT NOT NULL DEFAULT 0 )
Y

Has the ID column configured as PK, NN and AI, be the first column on my table definition?
SQL, Database Testing • mysql database design table mysql 5.6 alter table • • yajahira

2

0
Votes

2
Posts

0
Views

J

No. The order of columns in a table definition does not matter, with one old exception. In older versions the "first" TIMESTAMP column had a special default DEFAULT value. It is not required to have an AUTO_INCREMENT. However it is (essentially) required that you have a PRIMARY KEY. It smells like AgreementNumber would be such? Keep in mind that a PK must be UNIQUE (a MySQL requirement). As a side note, an A_I does not have to be the PK. The only requirement is that it be the first column in some index. And remember that a PK is a unique key is a key. But, that is not your 'real' problem. That is, you need to do LOAD DATA without the id being provided. Plan A: Load into another table, then copy it over. When copying the data over, specify all the columns except the id; A_I will take care of the rest. Plan B: Use LOAD DATA, but specify all the columns except id. Again A_I will take care of defaulting it correctly. Other tips, while I have you on the line: int(1) is a 4-byte number; the (1) means nothing. Perhaps you want a 1-byte TINYINT? Use INT UNSIGNED where appropriate Use datatypes smaller than INT where appropriate; look up MEDIUMINT, and again make it MEDIUMINT UNSIGNED if appropriate. Use NOT NULL when appropriate. Don't index flags; they won't be used. Learn about "composite" indexes. More If you choose to have a 6-column UNIQUE key that includes some VARCHARs, having an A_I is probably better than making that the PK. A UNIQUE KEY is two things: An index, and a uniqueness constraint. You can use it for either or both. A PK is a UNIQUE KEY that identifies the row. It also is "clustered" with the data, making lookups more efficient than with a "secondary key" (any non-PK). When the 'cardinality' of an indexed column is very low, such as for a flag, the Optimizer will dynamically look at the value you provide and see if less than 20% of the table has that value. In this case, it will use the index; else it will not. The "20%" varies with the phase of the moon. The reason for eschewing the index is that using the index involves bouncing back and forth between the index's BTree and the data's BTree via the PK. This can be slower than simply scanning the data, tossing 80% of the rows. A secondary key is a BTree containing the indexed column(s) plus a copy of the PK.

one table is related to two other tables is that a good idea?

Suggested Topics

When normalizing a relational database, should multiple "type" tables be combined into one? SQL, Database Testing • postgresql database design normalization relational theory • • baileigh

Best way to design this mileage table? SQL, Database Testing • database design sql server 2008 sc languagehema • • elysha

How to set-up a distributed data storage system for The Real Junkfood Project SQL, Database Testing • database design users database rec languageomme • • guilherme

Design: How do you decide when items are related vs when they are attributes? SQL, Database Testing • database design erd • • shizuka

join the two tables which possess one to many relationship SQL, Database Testing • join rdbms • • Kadyn

Should I declare one or two tables to represent Items and Sub Items? SQL, Database Testing • mysql database design • • Duquan

Store row count or use query to calculate on Primary Key SQL, Database Testing • mysql database design normalization • • carriann

How to handle versions of an Entity in Database Design SQL, Database Testing • database design erd version c languageontrol • • briley

Row for each date, or 31 columns? SQL, Database Testing • mysql database design normalization • • yajahira

Access IFf Query SQL, Database Testing • database design update • • Duquan

store two tables into a single table in mySQL SQL, Database Testing • mysql • • esther

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table? SQL, Database Testing • sql server performance query performance sql server 2016 merge data sync languagehroniza • • Rossere

Review of Log Messages Database Schema SQL, Database Testing • database design • • Trenton

Using a many-to-many table with a one-to-many relation? SQL, Database Testing • mysql database design • • Duquan

Has the ID column configured as PK, NN and AI, be the first column on my table definition? SQL, Database Testing • mysql database design table mysql 5.6 alter table • • yajahira

When normalizing a relational database, should multiple "type" tables be combined into one?
SQL, Database Testing • postgresql database design normalization relational theory • • baileigh

Best way to design this mileage table?
SQL, Database Testing • database design sql server 2008 sc languagehema • • elysha

How to set-up a distributed data storage system for The Real Junkfood Project
SQL, Database Testing • database design users database rec languageomme • • guilherme

Design: How do you decide when items are related vs when they are attributes?
SQL, Database Testing • database design erd • • shizuka

join the two tables which possess one to many relationship
SQL, Database Testing • join rdbms • • Kadyn

Should I declare one or two tables to represent Items and Sub Items?
SQL, Database Testing • mysql database design • • Duquan

Store row count or use query to calculate on Primary Key
SQL, Database Testing • mysql database design normalization • • carriann

How to handle versions of an Entity in Database Design
SQL, Database Testing • database design erd version c languageontrol • • briley

Row for each date, or 31 columns?
SQL, Database Testing • mysql database design normalization • • yajahira

Access IFf Query
SQL, Database Testing • database design update • • Duquan

store two tables into a single table in mySQL
SQL, Database Testing • mysql • • esther

Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?
SQL, Database Testing • sql server performance query performance sql server 2016 merge data sync languagehroniza • • Rossere

Review of Log Messages Database Schema
SQL, Database Testing • database design • • Trenton

Using a many-to-many table with a one-to-many relation?
SQL, Database Testing • mysql database design • • Duquan

Has the ID column configured as PK, NN and AI, be the first column on my table definition?
SQL, Database Testing • mysql database design table mysql 5.6 alter table • • yajahira