Database Design - 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

Import/Export (DTS) and Replication (2000)

Database Design

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-13 : 09:23:27

Andy writes "OS : Windows 2003
SQL Server 2000

I have a database that the user will upload data to from their existing data store ( excel files or CVS file )
Here is a sample database design that i have currently.


tablSystem ( differnt type of systems )
system_id system_type
1          System 1
2          System 2

tableClient (all clients for different types of system )
client_id system_id clientname
1           1          AA
2           1          BB

tableBuilding ( buildings for different clients of different systems )
building_id client_id system_id 
1              1           1
2              1           1
3              2           1  
tableFloor ( building floors )
floor_id building_id client_id system_id
1             1           1         1
2             1           1         1

tableRoom ( rooms on floors )
room_id floor_id client_id system_id
1          1         1          1
2          1         1          1

Now the problem i am having is as follows:
When the user will upload their data from their existing data store to the above mentioned tables( as mentioned above ) their unique id will not be matching the primary key ( the last id )that i have in my database. once they upload the data to the database, the records also need to be linked across each other.
how do i accomplish this."

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-07-14 : 16:23:00

So the users are loading system-building-floor-room data?

tableClients (Client_id(PK); etc...)
tableSystem (System_id(PK); etc...)
tableBuilding (Building_id(PK); etc...)
tableFloor (Floor_id(PK), Building_id(FK); etc...)
tableRoom (Room_id(PK), Floor_id(FK); etc...)
tableData (Data_id, Client_id, System_id, Building_id)

or

tableClients (Client_id(PK); etc...)
tableSystem (System_id(PK); etc...)
tableBuilding (Building_id(PK); etc...)
tableFloor (Floor_id(PK), etc...)
tableRoom (Room_id(PK), etc...)
tableData (Data_id(PK); Client_id(FK); System_id(FK); Building_id(FK); Floor_id(FK); Room_id(FK))

It's going to depend on how you use the data, and what makes sense for the data. You may also want to look up database normalization.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources