 code green  1,726
Recognized Expert Top Contributor
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_compat ability` 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
10 2220  Atli 5,058
Recognized Expert Expert
...
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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 (e.g. John Smith) Each User would contain a following Group of
tables
a)Customers
|
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 Article
{
int articleID;
string title;
//Constructor: Load Data from DataAccess Class
|
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 generic design patterns that can be
used and shared amongst many sub-schemas.
For example, the grouping of entities. I may have the following tables:
employee, product and client. These tables have no direct relationship
with each other. But...
|
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
|
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 me.
Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the
query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog.
Is there any way...
| | |
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 hierarchcal structure where any
node can be linked with a document file, which are all stored in a SQL
Server database.
I'm trying to convince my boss towards smartclient instead of ASP.NET
|
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 one
etc.....),building ER diagrams,proper usage of ER diagrams in our
database(Primary key foreign key relations),designing small
modules,relating tables and everything that relates about database
design....Coz I think database design is the...
|
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 Model to
Capture More Meaning (1979)
# Addison Wesley - Database Design for Mere Mortals chm
# Addison Wesley - Refactoring Databases Evolutionary Database Design
(2006)
# Apress Beginning Databases with PostgreSQL From Novice to
Professional...
|
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. This seemingly happens randomly. Out of all of the database I have worked with, this has happened on 3 of them - several times randomly to each. All three of the databases that have exhibited this behaviour have been databases I have written for the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
