Help with database design
-
Hi all,
I'vebeen using an excel spreadsheet to capure info on oranistions and vehcile related data and would like to tranfer this into the Microsoft dataverse environment. As you can probably tell, my database skills aren't as good as my excel skills.
I'd be grateful if someone could cast there eyes over the design below for the first part of my design and provide some feedback on whether or not I have the PK and FK relationships correct and if I could simplify the design.
Thanks
Dave
-
Sample data would help, but it appear that relationships to UserOrgType & UserOrgSiteType are wrong.
userOrgTable should probably have:
- userOrgID
- userOrgName
- userOrgTypeID: Assuming there is only one type per organization. If many to many, then you need an association table.
userOrgType should probably have:
- userOrgTypeID
- userOrgType
userOrgSiteType should probably have:
- userOrgSiteTypeID
- userOrgSiteType
- userOrgTypeID: If there is only one org type per site type. If many to many, then you need an association table.
deptTable references userOrgSiteID, but didn't include userOrgSite table. That table would presumably have userOrgID, addressID. Or is that really addressTable (no need to include "Table" in the name of a table). If it refers to address table, then use AddressID.
addressTable: Should UserOrgAddress be StreetAddress?
Try to be consistent in naming/casing for tables & columns. Some start capitalized, others start lower-case.
-
You probably could/should simplify SiteType to not be organization specific -- e.g.,
- HQ
- Production
- Workshops
Then it doesn't need OrgTypeID.
-
Tables are typically plural.
I don't see why "user" should be in any of these names.
So maybe:
orgs ( orgId int, orgName varchar(100) )
orgTypes ( orgTypeId smallint, orgType varchar(50) )
orgSiteTypes ( orgSiteTypeId smallint, orgSiteType varchar(100) )
addresses ( addressId int, addressLine1 varchar(50), addressLine2 varchar(50), addressLine3 varchar(50), postcode char(6) )
--PostCode varchar(6), interesting, Canada maybe?
orgSites ( orgSiteId int, orgId, orgSiteTypeId, addressId )
departments ( departmentId smallint, departmentName varchar(100) )
orgSiteDepartments ( orgSiteId, departmentId )
The site / dept / contacts still needs some fleshing out.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Definitely remove the word "Table" from the end of your table names. You'll also have Joe Celko coming in and giving you a hard time if you don't!
I believe it's acceptable to use either singular or plural names for database table names, but once you've decided on a naming convention, it's important to maintain consistency throughout the entire database. Using singular names can simplify the process of naming primary keys, as the "Id" column can simply take the format of "tablenameId". However, if plural names are used, the "s" at the end of the table name would need to be removed to create the primary key name. This can make it more challenging to determine the primary key for a given table, especially for tables with irregular plural names such as "Addresses" or "Sundries". As a result, automating scripts or processes for these tables may require additional effort.
-
Hi all,
Thank you for your feedback, all very much appreciated. I will take you commenst on board and restructre as suggested.
Thanks
Dave
-
Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr. won't even be getting his abbreviated name on your database.
- Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
- Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
Yes, and I_S tables use plural form. If you use singular for tables, it looks rather amateurish to me.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
Yes, and I_S tables use plural form. If you use singular for tables, it looks rather amateurish to me.
Why does it look amateurish to you?
- Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
Yes, and I_S tables use plural form. If you use singular for tables, it looks rather amateurish to me.
Why does it look amateurish to you?
Because professionals -- like MS, Oracle, ANSI, etc. -- use plural names for tables. Btw, they also (generally) avoid the God-awful camel case and similar structures. On a case-sensitive db -- and I've worked on some -- camel case is a real pita. Different people upper/lower differently. Hell, the same person will do it differently one day/week to the next!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
Yes, and I_S tables use plural form. If you use singular for tables, it looks rather amateurish to me.
Why does it look amateurish to you?
Because professionals -- like MS, Oracle, ANSI, etc. -- use plural names for tables. Btw, they also (generally) avoid the God-awful camel case and similar structures. On a case-sensitive db -- and I've worked on some -- camel case is a real pita. Different people upper/lower differently. Hell, the same person will do it differently one day/week to the next!
I thought I'd check with ChatGPT:
System tables, such as INFORMATION_SCHEMA.TABLES, are used to store metadata about the database objects, such as tables, views, columns, and constraints. These tables are created and maintained by the database management system (DBMS) itself, and their contents are updated automatically whenever a new database object is created, modified, or deleted. Users typically do not insert, update, or delete data in these tables, but rather query them to retrieve information about the database schema and its objects.
User-defined tables, on the other hand, are created by the user to store data that is specific to the application or business logic. Users typically insert, update, and delete data in these tables as part of the application's normal operation.
Given these differences in usage, it is common practice to use singular names for user-defined tables to reflect that each row represents an instance of the entity that the table describes. Plural names are often used for system tables, on the other hand, to reflect that they contain information about multiple instances of various entities in the database schema. However, as I mentioned earlier, the choice of singular or plural names for tables is a matter of convention and personal preference, and there is no hard and fast rule that dictates how tables should be named.
- Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:ScottPletcher wrote:Jonathan AC Roberts wrote:
I believe it's acceptable to use either singular or plural names for database table names,
Acceptable, perhaps. But can you name any major RDBMS that uses singular table names in the table / view names it provides you to use for the system? Such as sys.columns, INFORMATION_SCHEMA.tables, etc.. (OK, admittedly Oracle is inconsistent there and has a few singular table names, but the vast majority are plural.)
I can't but INFORMATION_SCHEMA is an ANSI-standard so all databases will have the same named views/tables for this.
Yes, and I_S tables use plural form. If you use singular for tables, it looks rather amateurish to me.
Why does it look amateurish to you?
Because professionals -- like MS, Oracle, ANSI, etc. -- use plural names for tables. Btw, they also (generally) avoid the God-awful camel case and similar structures. On a case-sensitive db -- and I've worked on some -- camel case is a real pita. Different people upper/lower differently. Hell, the same person will do it differently one day/week to the next!
I thought I'd check with ChatGPT:
System tables, such as INFORMATION_SCHEMA.TABLES, are used to store metadata about the database objects, such as tables, views, columns, and constraints. These tables are created and maintained by the database management system (DBMS) itself, and their contents are updated automatically whenever a new database object is created, modified, or deleted. Users typically do not insert, update, or delete data in these tables, but rather query them to retrieve information about the database schema and its objects.
User-defined tables, on the other hand, are created by the user to store data that is specific to the application or business logic. Users typically insert, update, and delete data in these tables as part of the application's normal operation.
Given these differences in usage, it is common practice to use singular names for user-defined tables to reflect that each row represents an instance of the entity that the table describes. Plural names are often used for system tables, on the other hand, to reflect that they contain information about multiple instances of various entities in the database schema. However, as I mentioned earlier, the choice of singular or plural names for tables is a matter of convention and personal preference, and there is no hard and fast rule that dictates how tables should be named.
Presumably you carefully tailored the q to get such an odd response.
In ALL tables, a row represents an "instance" of the entity the table describes. ANY table can contain "multiple instances" (i.e. multiple rows). The "distinction" attempting to be made there just makes NO logical sense.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Not that carefully. I've just typed in roughly the same question as I can't remember exactly what it was, also ChatGPT has some randomness built in so doesn't arrive at exactly the same answer twice.
I agree with you that there is no difference in that a row of INFORMATION_SCHEMA.TABLES represents a table and a row of a "Customer" table represents a customer. I just pasted in the results from ChatGPT.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply