473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Is this correct database design?

code green
1,726 Expert 1GB
I wasn't sure what title to give this, or whether I can explain my dilemma, but here goes.
My company sells products that are inter-compatible with each other, mainly four ranges I will call A,B,C,D.
A key table in the database is `product_compatability` with two columns listing product IDs that are compatible with each other.. At the moment it lists the compatibility between A-B with A in col 1 and B in col 2. Straightforward.
This must now be expanded, with possible combinations of A-B; A-C; A-D; B-C; B-B; B-D; but no possible match C-D.
The dilemma now is the rule of A in column 1 and B in column 2 breaks down, as B can be in either and BOTH. Thereby complicating queries SELECT INSERT and UPDATE.
Our answer to avoid UNION the table with itself in every query is to double the size of the table and mirror each record with itself so it does not matter about the column order. The table goes from 30000 records to 60000 but queries are less complicated.
I sit uncomfortable with this. In effect every record is duplicated but in reverse order. However queries should be faster, I think!
Is this correct database design or is a rethink in order?
Jun 18 '07 #1
10 2195
Atli
5,058 Expert 4TB
Ok, Im trying to understand this but there is one thing that's bothering me. You list all possible combinations of compatible products, but you include 'B-B'.
How does that make sence?
Jun 19 '07 #2
code green
1,726 Expert 1GB
I admit this is confusing.
The letters represent a class of products, not individual products..
And the combination B-B means the two parts are interchangeable rather than compatible with each other.
Jun 19 '07 #3
Atli
5,058 Expert 4TB
Ok,
So, assuming that each product cluster has a auto_increment primary key, you could create a rule that states that the smaller ID should always be placed in the first column, and the larger in the second one.
(This rule could be adjusted to any primary key, like say in alphabetic order if that would fit your primary keys better.)

But lets assume for now, that I have a table, called 'ordTest' that was created like this:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE ordTest
  2. (
  3.   val1 BIGINT NOT NULL,
  4.   val2 BIGINT NOT NULL,
  5.   PRIMARY KEY(val1, val2)
  6. )
  7.  
Then you could create a procedure that would insert into that table, putting the smaller ID into the first column and the bigger into the second.
Expand|Select|Wrap|Line Numbers
  1. delimiter $
  2. DROP PROCEDURE IF EXISTS InsertCompatible$
  3. CREATE PROCEDURE InsertCompatible(x BIGINT, y BIGINT)
  4. BEGIN
  5.   IF x < y THEN
  6.     INSERT INTO ordTest(val1, val2) VALUES(x, y);
  7.   ELSE
  8.     INSERT INTO ordTest(val1, val2) VALUES(y, x);
  9.   END IF;
  10. END $
  11. delimiter ;
  12.  
Then you can create another procedure to check wheter any given ID numbers are compatible
Expand|Select|Wrap|Line Numbers
  1. delimiter $
  2. DROP PROCEDURE IF EXISTS IsCompatible$
  3. CREATE PROCEDURE IsCompatible(xID BIGINT, yID BIGINT)
  4. BEGIN
  5.   DECLARE x INT;
  6.   IF xID < yID THEN
  7.     SELECT COUNT(*) INTO x FROM ordTest WHERE val1 = xID AND val2 = yID;
  8.   ELSE
  9.     SELECT COUNT(*) INTO x FROM ordTest WHERE val1 = yID AND val2 = xID;
  10.   END IF;
  11.   SELECT x != 0 AS 'RESULT';
  12. END $
  13. delimiter ;
  14.  
Now, all you have to do to insert compatible products classes into the table is call the first procedure, like this:
Expand|Select|Wrap|Line Numbers
  1. CALL InsertCompatible(1, 2);
  2.  
Note, that because of the two columns both acting as Primary Key's, you can not insert duplicate products using this procedure.

And to check if any two ID's are compatible you can call the second procedure:
Expand|Select|Wrap|Line Numbers
  1. CALL IsCompatible(1, 2);
  2.  
Which would output a table with a singe col called 'RESULT' and contain a single row with a boolean value, indicating whether the ID's match.
Jun 19 '07 #4
code green
1,726 Expert 1GB
Thanks Atli,
I'll study this and reply next couple of days.
But if I am doing a select,update or delete on a particular ID,
then will both fields be included in every query with a self join or a union?.
Jun 19 '07 #5
Atli
5,058 Expert 4TB
Thanks Atli,
I'll study this and reply next couple of days.
But if I am doing a select,update or delete on a particular ID,
then will both fields be included in every query with a self join or a union?.
I'm not entirly sure what you mean.
The table works just as any other table, except both fields are joined to form a primary key.
You can select or modify them seperately as you wish, as long as neither of them ends up empty and when they are combined, they must be unique.
Jun 19 '07 #6
code green
1,726 Expert 1GB
It is not uniqueness of table entries that concern me, as both fields are set up as primary keys. It is a balance of performance and memory usage. Do I go for this structure
Expand|Select|Wrap|Line Numbers
  1. product_id    compatible_product_id
  2.     101                  303
  3.     101                  307
  4.     102                  305
  5.     102                  303
  6.     303                  307
Or this structure
Expand|Select|Wrap|Line Numbers
  1. product_id    compatible_product_id
  2.     101                  303
  3.     101                  307
  4.     102                  305
  5.     102                  303
  6.     303                  102                  
  7.     303                  101                  
  8.     307                  101                  
  9.     305                  102 
  10.     303                  307
  11.     307                  303
The first LOOKS more efficient until you start to write queries, for example to SELECT all products compatible with id 303. The current queries will still work with the second design but need rewriting to check both fields in the first.
The second design does not need any stored procedure or knowledge of any order of INSERT rules. But INSERTs and DELETEs have to be executed twice with the second. I am simply looking for somebody's opinion
Jun 22 '07 #7
thodos
1
Since A is compatibile with B implies B is compatible with A then it doesn't matter which column holds A or B. A single row (A,B) or (B,A) or both rows implies A is compatible with B.

Atli's insert and delete procedures would eliminate duplicate entries in the table.

Since it doesn't matter which column A is in, you need to select values from either column, so the select would be something like:
select distinct ("product id" (if not :A), else "compatible product id")
from table
where "product id" = :A or "compatible product id" = :A

This should give you the other product when one is A.
Jun 22 '07 #8
Atli
5,058 Expert 4TB
It is not uniqueness of table entries that concern me, as both fields are set up as primary keys. It is a balance of performance and memory usage.
Ok, I see what you mean now.

I'm not sure, but I would think a more complicated query executed on x ammount of rows would run faster that a simple one on x*2 rows.

Thats mainly because queries are most often just collections of boolean expressions that MySQL executes using the data it has, and I'm pretty sure it takes more time for MySQL to fetch extra rows that it takes to execute extra boolean expressions.

It would probbly depend on you queries, so you should really test both methods using random data and compair the results.
Jun 23 '07 #9
code green
1,726 Expert 1GB
Thanks for the replies.
This is just one table of about 50 in the whole database.
This query looks simple
Expand|Select|Wrap|Line Numbers
  1. select distinct ("product id" (if not :A), else "compatible product id")
  2. from table
  3. where "product id" = :A or "compatible product id" = :A
but becomes quite complicated when other tables are JOINed to pull in a product_name or product_description for example.
And UPDATEs would be even more troublesome.
As I said, I will need little modification of my current queries if I go with the second design with the mirror data..
To be honest I was hoping somebody would tell me off for even considering one of these designs.
Oh well, i will go with the mirrored data idea, if only to save extra work on my part. Thanks again.
Jun 25 '07 #10
Atli
5,058 Expert 4TB
...
To be honest I was hoping somebody would tell me off for even considering one of these designs.
...
Hehe, under normal circumstances I would have, but your problem has no easy solution (I can think of).
Unusual circumstances call for unusual measures.
Jun 27 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users...
4
by: cmo63126 | last post by:
I'm not sure if this is bad design or not. It seems flawed, but I'm not sure. Is it wrong to create an instance of a class within the class itself? Any feedback appreciated. public class...
5
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of...
5
by: mm nn | last post by:
Hi, I want to create a table like this: ID Autonum Datefld Date Cat Text Itm Text tCount Number
9
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for...
1
by: MuZZy | last post by:
Hi, I'm going to develop a pretty simple document management application and i want to use WCF to connect back-end to smart client front-end. That application will allow to create a...
3
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to...
0
by: Laurynn | last post by:
# (ebook - pdf) - programming - mysql - php database applicati # (Ebook - Pdf)Learnkey How To Design A Database - Sql And Crystal Report # (ebook-pdf) E F Codd - Extending the Database Relational...
2
by: programmerx101 | last post by:
Ok, I'm looking for expert advice on this one. I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.