 |
|
|
|
Next: Poor DELETE performance in SQL Server 2000 with m..
|
| Author |
Message |
External
Since: Jan 25, 2008
Posts: 8
|
(Msg. 1) Posted: Fri Jan 25, 2008 6:07 am
Post subject: Basic table design
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi
I Hope this is the right place to ask a simple table design question.
How would I implement an order consisting of a number of order lines in a
database.
Here is my best shot.
Order table
OrderID (PK)
Date, Soldby etc.
OrderLines tabel
OrderID (FK) (PK, part 1 of 2)
LineNumber (PK, part 2 of 2)
Quantity, etc.
Is that a good design or is there a better design? |
|
| Back to top |
|
 | |
External
Since: Jan 11, 2008
Posts: 1089
|
(Msg. 2) Posted: Fri Jan 25, 2008 1:47 pm
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
CREATE TABLE OrderDetails
(order_nbr CHAR(10) NOT NULL
REFERENCES Orders(order_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
sku_code CHAR(10) NOT NULL
REFERENCES Inventory(sku_code),
PRIMARY KEY (order_nbr, sku_code),
order_qty INTEGER NOT NULL
CHECK(order_qty > 0)
..);
Why did you think that a physical counter inside the hardware could
ever be part of a logical model? Where is the item being ordered in
your model? |
|
| Back to top |
|
| |
External
Since: Jan 11, 2008
Posts: 9
|
(Msg. 3) Posted: Fri Jan 25, 2008 3:13 pm
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
koger wrote:
> Hi
>
> I Hope this is the right place to ask a simple table design question.
>
> How would I implement an order consisting of a number of order lines in a
> database.
> Here is my best shot.
>
> Order table
> OrderID (PK)
> Date, Soldby etc.
>
> OrderLines tabel
> OrderID (FK) (PK, part 1 of 2)
> LineNumber (PK, part 2 of 2)
> Quantity, etc.
>
> Is that a good design or is there a better design?
PK is best when constantly growing. Better will be:
OrderDetailes
OrderDetailID int identity PK
OrderID FK
Quantity, etc. >> Stay informed about: Basic table design |
|
| Back to top |
|
| |
External
Since: Jan 25, 2008
Posts: 8
|
(Msg. 4) Posted: Fri Jan 25, 2008 3:13 pm
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thank you for your answer.
> PK is best when constantly growing. Better will be:
OK, can you tell me why, is it because of performance or design?
> OrderDetailes
> OrderDetailID int identity PK
> OrderID FK
> Quantity, etc.
I'm sorry but I don't quite understand you example, could you elaborate a bit?
Thanks >> Stay informed about: Basic table design |
|
| Back to top |
|
| |
External
Since: Jan 11, 2008
Posts: 9
|
(Msg. 5) Posted: Fri Jan 25, 2008 3:30 pm
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
koger wrote:
> Thank you for your answer.
>
>> PK is best when constantly growing. Better will be:
> OK, can you tell me why, is it because of performance or design?
>
performance reasons, if your PK constantly grows, no fragmentation of index
>
>> OrderDetailes
>> OrderDetailID int identity PK
>> OrderID FK
>> Quantity, etc.
> I'm sorry but I don't quite understand you example, could you elaborate a bit?
>
> Thanks
OrderDetailID is integer identity somewhere called Autoincrement. It
starts with 1 and grows by 1. Consider using bigint if you plan to have
many Order lines. >> Stay informed about: Basic table design |
|
| Back to top |
|
| |
External
Since: Jan 12, 2008
Posts: 44
|
(Msg. 6) Posted: Tue Jan 29, 2008 6:16 am
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Did you answer in the wrong thread?
"--CELKO--" wrote in message
> CREATE TABLE OrderDetails
> (order_nbr CHAR(10) NOT NULL
> REFERENCES Orders(order_nbr)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> sku_code CHAR(10) NOT NULL
> REFERENCES Inventory(sku_code),
> PRIMARY KEY (order_nbr, sku_code),
> order_qty INTEGER NOT NULL
> CHECK(order_qty > 0)
> ..);
>
> Why did you think that a physical counter inside the hardware could
> ever be part of a logical model? Where is the item being ordered in
> your model? >> Stay informed about: Basic table design |
|
| Back to top |
|
| |
External
Since: Jan 17, 2008
Posts: 13
|
(Msg. 7) Posted: Tue Jan 29, 2008 4:43 pm
Post subject: Re: Basic table design [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
> Why did you think that a physical counter inside the hardware could
> ever be part of a logical model? Where is the item being ordered in
> your model?
Can you elaborate? >> Stay informed about: Basic table design |
|
| Back to top |
|
| |
| Related Topics: | table design help - hey all, salary table ------------- salary date, Salary employment history table ----------------------------- job title, employment date let's say i want to pick a record in the history table and find out what salary they were making at the time....
Suitable Table Design ? - Hello David, Would (contract_num,details_changed_date) key be efficient in Queries or (contract_num,AmendId) key be efficient in Queries - Note that AmendId is a sequence number for each of the amendments. Gopi "David Portas"..
need help with table design - opinions please - i have a table called ORDERQUEUE laid out like this create table [ORDERQUEUE] (oq_id int identity primary key, customer_id int not null, grouping_id int null, queue_date datetime not null default getdate().... other columns here) clustered index is..
Table Design Question - I'm trying to figure out the best way to design a couple of tables given the following scenario. Here's how I currently have it configured.. Table 1 called Packages is defined as: Package Id smallint Groups varbinary(256) Table 2 is called....
Table design - really stuck! - I am in the process of figuring out the schema for this database. I have most of the tables already figured out and set up in an Excel file so it is easier for me to see how they will be set up. I don't have code for this yet, but I came across an.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
