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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 30, 2008
I guess I am confused about something and need some help. I am looking at a database schema for about 20 tables in a database. I noticed that the firstcolumn in each table Is some type of Id. For example StudentId,TestId etc.Where the Id is a unique numeric sequential value.
So I have some questions?1- Do these Id's act as what are called indexes for the table?(Unique indexes)2- If the answer to 1 is correct, then how do I create these unique indexes? Is it as simple as declaring the Id column as the primary key and that this value will be generated automatically upon insert? 3- Is it necessary to have an Id column for every table, or may I only do it for a few of them?4. In relation to Question2, what do I need to do, so that the Id column will automatically be created when someone inserts a value into the database table with an ID column.
For example here are two sample tables
StudentsInfo
StudentId<PK> Name Age 1 Mark 33 2 Jill 23 3 Mary 25
PersonalInfo
Name<FK> SocialSecurity Address MajorMark 324-444-3342 15 Elm ArtJill 888-888-8998 21 North ScienceMary 876-777-2344 18 Byle Music
View 6 Replies
View Related
Feb 26, 2008
Does it make sense to use one database to contain all tables for a Management System type application or to use multiple databases?
thanks,
Psion
View 2 Replies
View Related
Apr 3, 2008
I'm sure this is easy for someone with accounting system design. I'm toying with a design to handle pledged dolloar amount.
I have the following tables and design:
Pledges 1 to M Payment_Schedule
Pledges 1 to M Pledge_Payments
So basically I have three tables one for pledge info like amount to paid over time and how often (Monthly, Quarterly, Annual)
Then I have a Payment Schedule table to setup when pledges are due and when reminders should go out
Then a table to keep track of the payments received.
What I'm not sure of is how or even if I should relate a payment schedule with the pledge_payments table.
I appreciate any feed back or suggestions or better yet if there is a good design out there that someone will share with me.
View 1 Replies
View Related
