Dbdesign with many unique IDs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dbdesign with many unique IDs

Author

Topic

naits
Starting Member

9 Posts

Posted - 2003-12-09 : 13:24:33

I have a order/receipt/invoice system. The top of the system is the table Account. The tables Customer, Orders, Receipts and Invoice should give each related Account (with AccountID 10000) so that for example Order starts with OrderID 5000 and also AccountID 10001 can start with OrderID 5000.

My question is about if the simplest way to do this to include the relation AccountID in each of the four tables, or will it be OK to trust the relation down to Account. When I include AccountID in the table with for example OrderID, I get a programmatic way to check for duplicates.

To see the diagram: http://promotion.no/docshare/diagram_0912.gif

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 13:31:45

AccountID would be a foreign key in the child tables. Is that what you are asking?

Tara

naits
Starting Member

9 Posts

Posted - 2003-12-09 : 13:35:33

Yes, I know. But is it a good practice to include AccountID in for example the Order-table, or is it not necessary. How else to ensure on "SQLserver-side" that duplicates of OrderID not will occure on this special AccountID?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 13:37:32

Have you considered a unique constraint? Or how about have a composite primary key comprising OrderID and AccountID?

Tara

denisemc
Starting Member

26 Posts

Posted - 2003-12-09 : 13:39:56

OrderID should be the PK, so by definition it would be unique.

What I don't quite get is the difference between oID and OrderID, and cID and CustomerID?

Denise

naits
Starting Member

9 Posts

Posted - 2003-12-09 : 13:40:03

Where should I put the unique constraint? On Order - OrderID ? Can the unique constraint be used in a combination? How do I make a composite primary key comprising OrderID and AccountID? I'm using VS 2003

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 13:43:15

You need to create your database in Query Analyzer or Enterprise Manager, preferably QA if you are familiar with DDL commands. Yes multiple columns can be part of a unique constraint and a primary key.

Tara

naits
Starting Member

9 Posts

Posted - 2003-12-09 : 13:44:25

I want to technically allow duplicates of OrderID, but not in relationship with the same AccountID. Therefore I create a unique oID with autoincrement to reference it.

quote:
Originally posted by denisemc

OrderID should be the PK, so by definition it would be unique.

What I don't quite get is the difference between oID and OrderID, and cID and CustomerID?

Denise

denisemc
Starting Member

26 Posts

Posted - 2003-12-09 : 13:46:05

quote:
I want to technically allow duplicates of OrderID, but not in relationship with the same AccountID. Therefore I create a unique oID with autoincrement to reference it.

Why?

Denise

naits
Starting Member

9 Posts

Posted - 2003-12-09 : 13:54:08

Because one firm who logs in (the Account) may decide that they want to start their Order-nummeration at 5000. Another maybe wants to start at 100000... Then I programmatically have to get a OrderID for a new order. Like this:

>
ALTER PROCEDURE dbo.GetSingleOrderTopOrderID

(
@AccountID int,
@TopValue int OUTPUT
)

AS

SET NOCOUNT ON

SELECT @TopValue = MAX(Orders.OrderID) + 1
FROM Orders INNER JOIN
Customer ON Orders.cID = Customer.cID INNER JOIN
Account ON Customer.AccountID = Account.AccountID
WHERE (Account.AccountID = @AccountID)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-09 : 13:56:37

Composite primary key.

Tara

denisemc
Starting Member

26 Posts

Posted - 2003-12-09 : 14:17:27

quote:
Because one firm who logs in (the Account) may decide that they want to start their Order-nummeration at 5000. Another maybe wants to start at 100000...

interesting. so yes, if that's the requirement, then the composite unique constraint is the way to go.

Just a suggestion: you might want to evaluate the NULLable columns. It looks like there are a number of nullables that have defaults, and some others that probably should be required (is a customer address really optional?)

naits
Starting Member

9 Posts

Posted - 2003-12-10 : 04:47:18

And how would this composite unique constraint OR Composite primary key look? I want to check that it has increased by 1, and that there is no duplicates in this exact AccountID...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-10 : 12:06:29

What do you mean look? It would have the two columns in it. The duplicates would be prevented because of the constraint. Which column do you want increased by 1? Just set that column to the IDENTITY property. Have you built any databases in SQL Server before? If not, we can help you walk through the process in EM if that is what you are needing. Do you have that tool on your machine?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-10 : 12:42:31

quote:
Originally posted by naits

Because one firm who logs in (the Account) may decide that they want to start their Order-nummeration at 5000.

So the order number belongs to the firm

FirmId, OrderId is your composite key...

You don't want to allow dups....

Brett

8-)

naits
Starting Member

9 Posts

Posted - 2003-12-11 : 03:55:47

Thanks Brett... So what your're saying is that I've may want to put FirmID in all tables that should have their own nummeration. Like FirmID = 1000 may start with an InvoiceID = 5000 and FirmID = 1001 may also start with an InvoiceID = 5000? Then just put a primary key on them both; voila a composite primary key ?!?

Does this mean that FirmID and InvoiceID should be in the same table? If that is correct, I guess I still have to have "helping unique index" oID to reference to mye OrdersDetails because I want it to be a one-to-many relationship. I doubt that I also should put a relation to FirmID in OrdersDetails too... Or what?

And to Tara: I can't use the IDENTITY property, because the OrderID alone can allow duplicates.

GreatInca
Posting Yak Master

102 Posts

Posted - 2003-12-11 : 15:16:34

Shoudn't be a many to many relation between account and customer (Customers can have have more than account, accounts can have more than customer)?

Account---CustomerAccount
Customer--

Looks liek you're duplicating customer info for every ccount they have. Howabout a customer-only table and changing your current customer table's name to CustomerAccount

You can then pass down both CustomerID and AccountID fields to child (many side) tables. Plus your PK is already on those 2 fields (I assume you have a unique constraint on cID becuase it let you create a FK Reference to it).

Every table should have a PK. See a lot without one.

It is usually ok to pass down a PK field extra steps if you don't mind the extra coding to keep it up to date (and swapping change performance in favor of read performance). It is good have a normalize set of data and then denormalize by copying the fields to the lower table.

****************************************
Check out my pictures at http://www.pbase.com/GreatInca

Check out my company's site!
http://www.jobing.com (Market picker for first visit)
http://phoenix.jobing.com (corporate home market if you don't live in a covered market)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources