Design Flaw???

Aug 1, 2007

I think I might have made my relational database design wrong. As of right now I have three tables.

Users, Posts, Comments.

Users is related to posts, with u_id (primary key in users) being a foreign key in posts.

Posts is related to comments, p_id (primary key in posts) being a foreign key in comments.

Comments is related to users, with u_id (primary key in users) being a foreign key in comments.

As you can see, it creates a circle. The types of relationships are all right, I'm just worried that the diagram looks like the tables are in a circle because of the relationships. Is this ok? I wish I could attach a picture so you could see a diagram of the database.

View 4 Replies


ADVERTISEMENT

SQLAgentOperatorRole Flaw ?

Feb 7, 2007

When a windowsuser being member of the SQLAgentOperatorRole (and not being sysadmin) creates a job (the owner = the domain account), and he wants takes a look at it afterward, he's not able to modify it !?!?!?

This would mean that when I want a user to be "jobmanager" for a server I need to make him sysadmin !!! ???

Is this a flaw in the system ?

View 2 Replies View Related

Find Logic Flaw, Order Number Generator

Mar 15, 2000

This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)

How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?


CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
@nSQLError int,
@nRowCount int
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN

View 1 Replies View Related

DB Design :: Database Design For Matrix Representation

May 13, 2015

I have a scenario like below

Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1

How to design tables in SQL Server for the above.

View 2 Replies View Related

Database Design/query Design

Feb 13, 2002

Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:

-------
Fixture
-------
fix_id
fix_date
fix_played

----
Team
----
tem_id
tem_name

-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals

It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.

Fixture contains the details about the fixture like date and fixture id and has it been played

Team contains team info like team id, name, associated graphic

TeamFixture is the table which links the fixture to it's home and away team.

TeamFixture exists to prevent a many to many type relationship.

Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!

View 2 Replies View Related

DB Design :: Table Design For Packages

Aug 18, 2015

I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show.  How to design the table.. 

Product name   :  
Nestle milk |
Rainbow milk
packages  :
CTN,OTR, NOs |

CTN, NOs
Price:
50,20,5 |
40,6

(Remarks for your reference):CTN=10nos, OTR=4 nos  
| CTN=8 Nos

View 3 Replies View Related

Help Me Design My DB

Nov 7, 2003

Hi all.

I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)

I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)


My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)


1. When should I use IDENTITY field ?

2. If I do NOT - how can I lock a record when I add a new one

View 17 Replies View Related

DB Design

Jan 13, 2004

Hi,

I have 7 web forms wizard. after the user edit the fields, I need to keep a history log of the changed data only.

I am thinking of the following structure

log_id, user_id, field_id, old_value, new_value, transaction

Where field_id is a number indicates the changed field.
for example 1> First Name, 2> Last Name etc

Where transaction is ADD, DELETE, EDIT

Also i need to keep history of the details
for example an enterprise has 10 branches.
State, employees
NY, 1000
MD, 500

My log table will look like this
log_id, user_id, field_id, old_value, new_value, transaction
1, 1, 1, , Mick ,John , EDIT
2, 1, 3, ,NY , HI , EDIT
3, 1, 3, ,MD , , DELETE
4, 1, 4, ,500 , , DELETE
5, 1, 3, , ,NV , ADD
6, 1, 4, , ,700 , ADD


Is there a better way to log changes?

Thanks

View 1 Replies View Related

Design Q

Jan 16, 2004

I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".

I want some tips on my database design.

One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.

Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").

Any suggestions?

View 3 Replies View Related

Bad Design?

Feb 10, 2004

Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]


Program A creates the records for Table C. In the process it reads from Table A and Table B.

Program B reads record from Table C. Program B now needs [SalesmanEmail] field.

The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?

View 5 Replies View Related

Which Is Better Design?

Oct 15, 2004

hello,

i am working on a project with another guy as well. well, we have a table called lets say student and student_unit where student_unit holds the unit names the student is doing. and i was planning to use the same student_unit table to hold the result of that unit for that student as it is the case of adding one field but this guy who happens to be my boss thinks that result should be separated. but is it a better idea to make a separate table just for that result which is totally dependent on unit and student? that doesn't make any sense to me.

if asked why, he says for performance, modularity and object oriented approach and blah blah and honestly i think he knows nothing. so i would like u guys to tell him if there is really a need to create a separate table for attributes which is totally dependant on the same PK? having five more attributes on the same table hurts than creating a new table?

View 1 Replies View Related

Help With DB Design

Oct 27, 2007

I think I might have dug myself in a hole here, so could use some advice on database design.

Currently, I have a table that records meeting minutes. The design is:


Code:

minutesid | int | 4 | no nulls
minutescat | varchar | 255 | allow nulls
minutesnotes | text | 16 | allow nulls


What this does is set up a minutes category, such as "Members Present", and the notes for that category, such as "Mrs. Peacock, Mr. Green, Professor Plum, Colonel Mustard, Miss Scarlett". Each individual category has it's own ID.

I need to develop an archive of each of the minutes. So if there are 15 categories and associated notes for the 15 OCT 2007 meeting, how can I design the archive table to include all of those categories and notes into one record for 15 OCT 2007, then for 15 NOV 2007 and so on ...?

Hope that makes sense. Thanks in advance ...

View 5 Replies View Related

A Lil Help With DB Design

Sep 21, 2007

I have a table to store members. For each member, I have flags like manager, verified, etc. How should I be storing the flags? As individual columns in the same table and using bit datatype or creating a new table called MemberStatus and creating 1 row for each flag?

Thanx in advance.

Premal.

View 14 Replies View Related

Design DB

Mar 26, 2008

Hi,

I need to design a database, but i i just don know if i am doing it correctly.

Basically, i have an equipment which is sending me a lot of gauges readings (high limit, low limit, current level), electrical readings, hydraulic readings, alarms levels such as Fuel level, temperature level, etc etc, about 200 different types of readings, continuously. I may have different types of equipments and although most of the data are the same, some data types and its availability may be different too., depending on the type of equipment.

I want to keep this readings in a database, and this database will be updated constantly.

Should i create all of this different gauges readings in one big single table? If i do so, i only have one long row of data then for a particular equipment.If i put in different table, i can't see how i can create relationship among my tables with such kind of data.

Can you help on the possible ways of putting this equipment real time readings into DB?

View 3 Replies View Related

I Need Help In Design?

Mar 12, 2004

I am trying to create a database that is used to create/store estimates for a manufacturing company. There are many things to consider in this estimate but I will isolate this question to the takeoff itself:

There are several categories in which costs are estimated and they are as follows : Product(s), Site Work, Transportation (Shipping), Field Materials, etc...

Should each of the above have their own "Takeoff" table, or would using one table and a gategory table be a better way to go?

Mike B

View 2 Replies View Related

How Would You Design This?

Jun 18, 2008

The data that I have is the following:

Car Manufacturer Factory CarCode
---------------- ------- -------
Ford Houston F-Hou
Ford Reno R-Hou
Chevy Houston C-Hou
Chevy Las Vegas C-LV
Honda SLC H-SLC
Ford SanFran F-SF
Chevy Miami C-Mia

I have a database design implemented already, but I'm receiving some disagreement about it from a co-worker. The way I see it is you have a car manufacturer and a factory location that are both unique, so I created a lookup table for each. I then created a branch table for the many-to-many relationship that exists between Manufacturer and Factory, and within that branch table I placed a column for CarCode, because it seems to me that Manufacturer and Location both determine the CarCode.

I also have an autonumber field within the branch table, which I then use for foreign key relationships.

Is there anything wrong with my design here?

Thank you in advance for your help!

View 5 Replies View Related

Design Help, Please...

May 1, 2007

I've fought with this issue for a week now and I can't come up with a simple way of doing it.

I have a database, with the following tables:
Members
MainCategories
Categories
Answers

The idea is that Members will log into the site, select the Categories from the MainCategories, fill out some data, which is stored in the Answers table. The problem is, each of the main categories have different fields. So a Main Category might be "Shoes" and the fields would be "Color,Size,Brand", and another Main Category might be "Cars" and the fields "Make,Model,Year,Color,Type,IsNew".

I thought about creating the Answers table with all the possible answers, linking it to the categories and members table, so we can view which members filled out answers for which categories.

Can anyone help me come up with a solution for this?

View 4 Replies View Related

Design Help

May 13, 2007

Please ignore my earlier post...

Please help me in designing the tables...
I used to get flat files which i need to import into a tables..
The flat files data which contains.....

FlatFiledata:
------------
100,1,xx,yy,,20030101
100,2,20030101
100,3,01,20030101


Below are the tables which i design please correct me..

Table1ForRow1
--------------
col Identity,
col1,100
col2,1
col3,xx
col4,yy
col5,,,
col6,20030101

Table1ForRow2
-------------
col Identity,
col1,100
col2,2
col3,20030101

Table1ForRow3
------------
col Identity,
col1,100
col2,3
col3,01
col4,20030101


When i queried then I want the output:
100,1,xx,yy,20030101,2,3,01

View 1 Replies View Related

Design

Jul 23, 2005

Hello all,I am trying to correctly model the relationship between products andversions within my db. The problem I have is how to store the versiondata. I need to collect the following information:1) Major Build Number (int)2) Minor Build Number (int)3) Build Number (int)4) SP number (int)What is the "correct" schema for this? How many tables and whatcolumns?(On a side note if any one has any links to useful examples of DBdesign could they post them?)Thanks,Jose

View 1 Replies View Related

Bad DB Design

Oct 30, 2007

I inherited a SQL 2005 database from my bro-in law that has tables with names in the spaces and spaces and other characters in the column names. I link to these tables in an MDB file. Access 2003 sees this fine, but Access 2007 returns odbc--call failed errors. Is there anything I can do short of removing the spaces and characters (? %) from all of the table and column names? Is there a switch I can throw that will tell Access 2007 to ignore this and work like a previous version?


Thanks.

View 5 Replies View Related

Help On DB Design

Jul 30, 2007

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

View 3 Replies View Related

DB Design Question

Sep 2, 2006

Hope this is in the right forum....Assume a DB design which has 3 entities Customers, Vendors, and Email-addresses.  Customers and Vendors can have zero, one, or many Email-addresses.  To  normalize the many-to-many, I have an intersecting entity called Email-addresses-usage.  I know how to handle the intersecting entity if it were only Customers and Email-addresses, i'm not sure about adding Vendors to the mix.My questions are:1) in sql managment studio, do I have a FK relationship from Customers to Email-addresses-usage and also a FK relationship from Vendors to Email-addresses-usage?2) If zero email addresses are allowed, how to I define a zero possibility from a relationship standpoint - i.e., do I just specify NOT enforing the FK constraint? but then, if email-addresses are present, I'd want the contraint to be enforced - how is this have my cake-and-eat-too accomplished?3) any tips/suggestions on how one might present this to an end user for data entry.Thanks

View 4 Replies View Related

Database Design???

Oct 20, 2006

Hello, I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..andwhat sort of service or products they can supply. For instance a Employment agency maybe under professional services  Table 1 Customer Customer_ID = primary key,,,, Sector_ID = Foreign keyComapany Name, Address, Phone, Postcode etcTabel 2 Industry SectorsSector_ID = primary key,,,,Customer_ID= foreign key banking, Education,Prof Services, etc  Table 3  Trading ActivityTrading_ID = primary key,,,,Sector_ID =  Foreign key, Products_ID= FkEmployment Agent, School, Lawyer etcTable 4 ProductsProducts_ID = primary key,,,,Trading_ID = foreign keySupply frozen foods, transport services, sports goods, etc  Table 5 Account Account_ID = primary key,,,,Customer_ID = foreign keyAccount Name, Credit Limit, Payment Terms, Open date, Account contact etc  One big point of confusion is, can I have the Customer_ID from the principal Customers tablein every table as a foreign key or must the tables be chained together one after the other as such. Advice appreciatedThanks 

View 1 Replies View Related

DB Design Advice

Jan 12, 2007

I'm creating a DB to track clients, programs, and client participation in the programs. They are service programs. A client can be in more than one program and a program can have more than one client.
Can someone give me an example of how they would layout the tables?
My guess is:
tblClient, ClientID
tblClientProgramLog, ProLogID, ClientID
tblProgramDetails, ProDetailID, ProLogID
tblPrograms, ProgramID, ProDetailID
I appreciate any suggestions,

View 4 Replies View Related

Design Question

Jan 15, 2007

What would the best / most correct way be to implement a relationship where you have for example a customer table,  a partner table and a orders table and both customers and partners can have orders associated with them.  This is just an easy way for me to describe the relationship I am looking at and is not really the data sets I am working with. 
Following this analogy I currently have customers and orders and the orders table has a column customer_id to link each order to a customer.   I now want partners to start to be able to place orders.  It does not seem logical to me to have a second order table for them but the two identity columns that are the id columns would be on separate tables and thus could conflict.  Only thing I can think of is to start the partner id identity column at a really high number.  Is this the right thing to do it somehow does not feel right. 

View 2 Replies View Related

Design Advise

Feb 14, 2007

I have been given a DB that currently stores Organizations. I need to add the ability to track multiple contacts for the  Organizations. The DB has several one-to-many relationships on the Organizations table, such as Documents and Notes. I would like to track these by contact as well as the organization.
Is it possible to add a tblcontact and a one-to-many relationships to the Organizations table as well as to the tblDocuments and tblNotes?
I appreciate any suggestions.
tblOrg (OrgID)
tblDocuments (DocID, OrgID)
tblNotes (NoteID, OrgID)
tblContact ???

View 3 Replies View Related

Database Design

Apr 10, 2007

Hi,
 I need a hand with designing a database.
 I am collecting results from a survey which has the following questions:
Call ref? How did you place your support call?  Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed? 1 = very satisfied and 10 = very unsatisfied.  How happy were you with the customer service you received upon placing the support call?   1 = very unhappy and 10 = very happy.How satisfied were you with the amount of time you had to wait until you heard from an engineer? 1 = very satisfied and 10 = very unsatisfied.   How satisfied were you with the time taken to get your problem/query resolved? 1 = very satisfied and 10 = very unsatisfied  Did you feel the engineer had enough knowledge to deal with your call? 1 = very good and 10 = not very good  Overall how satisfied were you with the support call placed? 1 = very satisfied and 10 = very unsatisfiedIs there anything we can do to improve the quality of the support and service you received?
I want to store this in a database.  Obviously I want to use best practice for design, normalisation etc.  The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database.  Any help appreciated!
Thanks
Andrew 
 

View 9 Replies View Related

Database Design

Apr 26, 2007

I am creating database tables for company testimonials.  Database columns: name, position, companyname, comment, service we provided. 
My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.
What is best practice for putting this db structure together?
Thanks
Andrew

View 1 Replies View Related

Design Of A Process

Jun 18, 2007

I'm going to design a web page for students parking permit application.I'm new to asp.net, I have a process in mind doing it like this:
The form asks student to choose a permit type, then fill in  applicant info, like name, grade, vehicle info, student may have 2 vehicles, and payment method, check or credit card,
I plan to create 3 tables like tblPermit, tblapplicant, tblVehicle.After the students fill in the form, click submit button, I pass the values and call a stored procedure, some thing like this.Dim Cmd As New Data.SqlClient.SqlCommand(MySQL, MyConn)
Cmd.CommandType = Data.CommandType.StoredProcedure
Then in the stored proceudre,  I will do :
Begin
insert into Permit table, then get permitID, by using something like this:Select @PermitID=@@Identity
Then insert into Student table, then use
Select @StudentID=@@Identity
Then insert into vehicle table.
All the above 3 processes I put it in begin, end--So that they will not messed up with other applicants data.
I think begin...end is one transaction.
SO am I doing correctly this way?  Thanks

View 2 Replies View Related

DB Design Connundrum

Jul 25, 2007

Hi,

I'm working on a database and have come into a situation like the following. I have a few tables that the program puts in new rows to store information at the current time. Once the row is in there it shouldn't be changed and needs to always reflect the same state it was in at the time it was entered. That table however makes use of information from other tables that contain items that will change over time from updates from the user. What I'm wondering is what is the best way to design the DB to be able to maintain the historically accurate records?

Considering tables of:

Record
----------
Record_ID
Data1
Data2
Data3
Item1_ID
Item2_ID

Item1
----------
Item1_ID
Data1
Data2
Item3_ID

Item2
----------
Item2_ID
Data1
Data2
Data3

Item3
----------
Item3_ID
Data1
Data2

The way I was thinking was to modify the Item tables to be in the format of:

Item#
----------
Item#_ID
Item#_OID (Original ID)
Data#...

And then whenever an update happened you would instead insert a new record to the table for that item with a new Item#_ID so that any new records would reference it however all the old records would still be reference the record that was there when they were created. The OID would contain the ID that the item was assigned when first created and would stay the same through all updates so we can identify the history of the item. Of course this method seems to have it's fair share of challenges. Any opinions?

View 2 Replies View Related

Sql Design Problem

Sep 13, 2007

I don't know if this is the place for this question; But I am going to ask anyways
I haver three tables:
Supplier(PK SupplierID int not null,  SupplierName varchar(30) not null)   
Product(PK ProductID int not null, ProductName varcchar(30) not null, UnitsInStock smallint allow null)
SupplierInvoice(PK SupplierInvoiceID int not null, SupplierID int not null FK, ProductID int not null FK, ProductQuantity small int )
What I want to do is to add products from SupplierInvioce ProductQuantity to Product UnitsInStock
in other words every time I buy products from Supplier  through SupplierInvioce.ProductQuantity  the quantity that I buy would be added to the Prodcuts.UnitsInStock
I just have no good idea about this, if to make another table named Stock and maybe be easier,  use a storeprocedure, or what, I'm kind of lost
Any sugestions will be appriciated,
 or is there a place where I can read about this?

View 2 Replies View Related

Database Design

Oct 29, 2007

 Can anyone tell me how can i design database architecture for the Table Category & Product...so that i can make N-level entity relation....I have database in SQL SERVER 2000. 

View 2 Replies View Related

SQL Design Question

Dec 5, 2007

I am a SQL novice and need to figure out if I need to draft some help from a SQL database guru.  I have an app to create and am unclear on where to place my editing constraints.  If I have multiple roles that need different levels of rights to records (some read only, some edit only, some create and edit), do I create those constraints at the SQL database level or do I use roles in the web page interface and filter accordingly?  I'm not sure which method is the proper way.  I have used roles in the past, but not differing levels of roles on the same record.
 Thanks for your help.
 
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved